using System;
using System.Collections;
using System.Collections.Specialized;
using System.Diagnostics;
using System.Drawing;
using System.Data;
using System.Data.OleDb;
using System.Reflection;
using System.Windows.Forms;
using PCSUtils.Utils;
using C1.Win.C1Preview;
using C1.C1Report;
using C1.Win.C1Chart;
using Section = C1.C1Report.Section;

using System.IO;
using Microsoft.Office.Interop;
using Range = Microsoft.Office.Interop.Excel.Range;
using Excel = Microsoft.Office.Interop.Excel;

namespace ProductionLineProductionProgressReport
{
	/// <summary>
	/// <author>Thachnn</author>
	/// This report is a very complex report.
	/// It combines severals .NET DataTables with C1Report and Interop with Excel to get some chart images.
	/// 
	/// 
	/// --- The first 3 rows: using Sum() function of C1Report VBScript to calculate. Sum() function work rather well.
	/// 
	/// --- Auto generate the DayInMonth, DayOfWeek serries (by using 2 fuctions of mine from ReportBuilder)
	/// 
	/// --- In the detail section:
	/// There are many fields put here (about 200fields). Some info-fields like PartNo, PartName, Model. .... and a large pack of indexed fields (from 1 till 31).
	/// They are fldPlanxx, fldAdjxx, fldActualxx, fldReturnxx, fldProgressDayxx, fldProgressAccumulatexx and fldAssessmentxx.
	/// We do not spread these 200 fields vertically. We group 'em into 6 rows, 
	/// Plan, Adj and Actual, Return, ProgressDay field are fill from tables (built in C# code). 
	/// Other fields like , Progress Accumulate, Assessment was calculate on render time, 
	/// (by VBScript on the C1Report XML layout file)
	/// But if we render in render time by C1, there are some problems we can't hanlde, C1 is rather bad if we process too much field calculating.
	/// 
	/// Actual -- get from dtbActualTable
	/// REturn -- get from dtbReturnTable
	/// --- all indexed fields will be re-render, re-spread depending on the number of day in month ( using the Spread function of mine in the ReportBuilder)
	/// 
	/// --- There are some sumRow() cell at the end of each line (in detail fields). 
	///			 They are generated by a small program. they have text like: fldPlan01+fldPlan02+ ...
	///			 These C1 VB may work well, but I really don't love it like the first time I see C1. Sometime it raise a very crazy unknown bsht.
	/// --- count "Achieve", count "Not Achieve" on each row is now process by the C1
	/// 
	/// --- Some field on the report is calculate by the VBScript (store on the report layout XML file). 
	/// For example: calc some percent cell on each detail row.
	/// Remember that the C1Report VBScript count() function is not work well , so in the last to rows (grand sum by day, at the bottom of report): AchieveDay and Not Achieve/Day, we should calculate these values by C# (and I did that)
	/// 
	/// --- About the chart:
	/// We put 3 first rows of report (PlanTotal, Actual Total, Progress Total) to the Excel files to generate the DetailChart
	/// We put AchievePercent (right most, bottom most field) to the "ProportionAchievePlan" Excel cell
	/// put ProportionStandard (right most, near top field) to the "ProportionStandard" Excel cell
	/// Then we copy 2 chart from Excel file to images, bind to the fldChart and fldTotalChart on the C1Report
	/// 
	/// The last line I drop here: DON"T BELIEVE IN 3rd Vendor COmponent Provider. C1REport is blsht when you processing huge report.
	/// </summary>
	[Serializable]	
	public class ProductionLineProductionProgressReport : MarshalByRefObject, IDynamicReport		            
	{
		#region IDynamicReport Implementation
	
		private string mConnectionString;
		private ReportBuilder mReportBuilder = new ReportBuilder();		
		private C1PrintPreviewControl mReportViewer;
		private bool mUseReportViewerRenderEngine = false;	

		private string mstrReportDefinitionFolder = string.Empty;
		string mstrReportLayoutFile = string.Empty;

		private object mResult;

		/// <summary>
		/// ConnectionString, provide for the Dynamic Report
		/// ALlow Dynamic Report to access the DataBase of PCS
		/// </summary>
		public string PCSConnectionString
		{
			get { return mConnectionString; }
			set { mConnectionString = value; }
		}

		/// <summary>
		/// Report Builder Utility Object
		/// Dynamic Report can use this object to render, modify, layout the report
		/// </summary>
		public ReportBuilder PCSReportBuilder
		{
			get { return mReportBuilder; }
			set { mReportBuilder = value; }
		}

		/// <summary>
		/// ReportViewer Object, provide for the DynamicReport, 
		/// allow Dynamic Report to manipulate with the REportViewer, 
		/// modify the report after rendered if needed
		/// </summary>
		public C1PrintPreviewControl PCSReportViewer
		{
			get { return mReportViewer; }
			set { mReportViewer = value; }
		}

		/// <summary>
		/// Store other result if any. Ussually we store return DataTable here to display on the ReportViewer Form's Grid
		/// </summary>
		public object Result
		{
			get { return mResult; }
			set { mResult = value; }
		}

		/// <summary>
		/// Notify PCS whether the rendering report process is run by
		/// this IDynamicReport 
		/// or the ReportViewer Engine (in the ReportViewer form) 
		/// </summary> 		
		public bool UseReportViewerRenderEngine { get { return mUseReportViewerRenderEngine; } set { mUseReportViewerRenderEngine = value; } }

		
		/// <summary>
		/// Inform External Process where to find out the ReportLayout	 ( the PCS' ReportDefinition Folder Path )
		/// </summary>				
		public string ReportDefinitionFolder
		{
			get 
			{
				return mstrReportDefinitionFolder;
			}
			set
			{
				mstrReportDefinitionFolder = value;
			}
		}


	
		/// <summary>
		/// Inform External Process where to find out the ReportLayout	 ( the PCS' ReportDefinition Folder Path )
		/// </summary>				
		public string ReportLayoutFile
		{
			get 
			{
				return mstrReportLayoutFile;
			}
			set
			{
				mstrReportLayoutFile = value;
			}
		}

		public object Invoke(string pstrMethod, object[] pobjParameters)
		{			
			return this.GetType().InvokeMember(pstrMethod, BindingFlags.InvokeMethod, null, this, pobjParameters);
		}


		#endregion
		public ProductionLineProductionProgressReport()
		{
		}	
	
		
		#region GLOBAL CONSTANT
		
		const string TABLE_NAME = "ProductionLineProductionProgressReport";	
		const string ASSESSMENT_OK = "O";
		const string ASSESSMENT_NG = "X";
		const string REPORT_NAME = "ProductionLineProductionProgressReport";
		short COPIES = 1;
		const string REPORTFLD_PARAMETER_CCN						= "fldParameterCCN";
		const string REPORTFLD_PARAMETER_MONTH					= "fldParameterMonth";
		const string REPORTFLD_PARAMETER_YEAR						= "fldParameterYear";			
		const string REPORTFLD_PARAMETER_ELEMENT		
			= "fldParameterProductionLine";
		const string REPORTFLD_PARAMETER_VERSION1			= "fldParameterVersion1";
		const string REPORTFLD_PARAMETER_VERSION2			= "fldParameterVersion2";
		const string REPORTFLD_PROPORTIONSTANDARDPERCENT	= "fldProportionStandardPercent";
		/// Result Data Table Column names, contain:
		/// PRODUCTID, CATEGORY,PARTNO,MODEL,BEGIN
		/// DATE
		/// PLAN+QUANTITY, ACTUAL+QUANTITY
		const string PRODUCTID = "ProductID";
		const string WORKORDERDETAILSTATUS = "WorkOrderDetailStatus";
		const int WORKORDERDETAIL_MFG_CLOSE = 3;

		const string CATEGORY = "Category";
		const string PARTNO = "PartNo";
		const string PARTNAME = "PartName";
		const string MODEL = "Model";
		const string BEGIN = "ProgressBeginQuantity";

		const string DATE = "Day";
		const string QUANTITY = "Quantity";	// suffix for PLAN,ACTUAL , RETURN column
		const string PREFIX_DAYINMONTH = "lblDayInMonth";
		const string PREFIX_DAYOFWEEK = "lblDayOfWeek";
		/// other constants			
		const string PLAN = "Plan";
		const string SO = "SO";
		const string WOBOM = "WOBOM";

		const string ADJ = "Adj";
		const string ACTUAL = "Actual";
		const string PROGRESSDAY = "ProgressDay";
		const string PROGRESS = "ProgressAccumulate";
		const string ASSESSMENT = "Assessment";
		//		const string RETURN = "Return";
		const string ROWCOUNTPASS = "RowCountPass";
		const string ROWCOUNTFAIL = "RowCountFail";
		const string ROWPERCENT = "RowPercent";

		const string FLD = "fld";		
		const string REPORTFLD_TITLE = FLD + "Title";
		const string PLANFAIL = "PlanFailD";
		const string PLANPASS = "PlanPassD";
		/// chart fields
		const string REPORTFLD_CHART	= "fldChart";
		const string REPORTFLD_TOTALCHART = "fldTotalChart";
		const string REPORTFLD_TOTALPASS = "fldPlanPassSumRow";
		const string REPORTFLD_TOTALFAIL = "fldPlanFailSumRow";		


		string META_TABLE_NAME = "MetaTable";
		string PLAN_TABLE_NAME_1 = "PlanTable1";
		string PLAN_TABLE_NAME_2 = "PlanTable2";
		string ACTUAL_TABLE_NAME = "ActualTable";
		string ADJ_TABLE_NAME = "AdjTable";
		string SO_TABLE_NAME = "SOTable";
		string WOBOM_TABLE_NAME = "WOBOMTable";
		string BEGINQUANTITY_TABLE_NAME = "BEGINQUANTITYTable";
		//string RETURN_TABLE_NAME = "ReturnTable";

		#endregion GLOBAL CONSTANT

		#region GLOBAL VAR	

		DataSet dstMAIN = new DataSet();	

		#endregion GLOBAL VAR

		/// <summary>
		/// Main function, generate the result data Table for the REPORT VIEWER
		/// Modify the REPORT VIEWER to display the report
		/// </summary>
		/// <param name="pstrCCNID"></param>
		/// <param name="pstrYear"></param>
		/// <param name="pstrMonth"></param>
		/// <param name="pstrPartyID"></param>
		/// <param name="pstrWorkOrderMasterID_1"></param>
		/// <param name="pstrWorkOrderMasterID_2"></param>
		/// <param name="pstrProportionStandard">You must fill 0.xx here ( a number less than 1 and greater or equal 0)</param>
		/// <returns></returns>
		public DataTable ExecuteReport(string pstrCCNID, string pstrYear, string pstrMonth, 
			string pstrProductionLineID /*report main element*/, 
			string pstrVersion_1, string pstrVersion_2, string pstrProportionStandard)
		{
			#region My variables						

			int nCCNID = int.Parse(pstrCCNID);
			int nMonth = int.Parse(pstrMonth);
			int nYear = int.Parse(pstrYear);			
			int nProductionLineID = int.Parse(pstrProductionLineID);
			int nVersion_2 = -1;	// if input is null or string.Empty, we get the maximum Version to calculate
			string paraVersion_2 = string.Empty;
			if(pstrVersion_2 == null || pstrVersion_2.Trim() == string.Empty)
			{
				nVersion_2	= GetMaxVersion(pstrCCNID);
				paraVersion_2 = "210012999";
			}
			else
			{
				nVersion_2	= int.Parse(pstrVersion_2);	
				paraVersion_2 = pstrYear + int.Parse(pstrMonth).ToString("00") + nVersion_2.ToString("000");
			}

			int nVersion_1 = -1;
			string paraVersion_1 = "1";
			if(pstrVersion_1 == null || pstrVersion_1.Trim() == string.Empty)
			{
				//nVersion_1 = GetPreviousVersion(pstrCCNID, pstrVersion_2);
				if(nVersion_2 <  1)
				{
					nVersion_1 = 0;
				}
				else
				{
					nVersion_1 = nVersion_2 - 1;
				}				
				paraVersion_1 = pstrYear + int.Parse(pstrMonth).ToString("00") + nVersion_1.ToString("000");
			}
			else
			{
				nVersion_1	= int.Parse(paraVersion_1);	
				paraVersion_1 = pstrYear + int.Parse(pstrMonth).ToString("00") + nVersion_1.ToString("000");
			}

			// not mandatory, so we will the default value 0.95 for other processing
			double dblProportionStandard = 0.95d;
			dblProportionStandard = ReportBuilder.ToDouble(pstrProportionStandard);			
			
			//  for display on the Report parameter Section
			string strReportParameter_CCN = string.Empty;
			string strReportParameter_ProductionLine = string.Empty;
			string strReportParameter_Version1 = (nVersion_1 < 0 ? pstrVersion_1 : nVersion_1.ToString()  );
			string strReportParameter_Version2 =  (pstrVersion_2 == string.Empty  ? (nVersion_2 + " (Lastest)" ) : pstrVersion_2);
						
			/// contain array of string: Name of the column (with days have data in the dtbSourceData)
			/// FOr Example:
			/// dtbSourceData contain: 01-Oct: has Plan Quantity
			/// 02-Oct has Actual Quantity
			/// So arrHasValueDateHeading contain: Plan01, Actual02
			ArrayList arrHasValueDateHeading = new ArrayList();				

			/// Keep count of PlanPass and PlanFail for all days (columns).
			Hashtable arrColumnPass = new Hashtable();
			Hashtable arrColumnFail = new Hashtable();

			// get data and cache all in the dstMAIN			
			dstMAIN = GetDataAndCache(pstrCCNID, pstrYear, pstrMonth, pstrProductionLineID, 
				paraVersion_1 , paraVersion_2 , pstrProportionStandard);	
			dstMAIN.DataSetName = pstrCCNID + pstrYear + pstrMonth + pstrProductionLineID + pstrVersion_1 + pstrVersion_2 + pstrProportionStandard;			

			System.Data.DataTable dtbMetaTable;
			dtbMetaTable  = dstMAIN.Tables[META_TABLE_NAME];		

			/* now ActualTable contains detail rows. 
			 *  We use detail row to decide which row in Plantable is MfgCLose, 
			 * and set PlanQty = CompletedQuantity (in ActualTable) if PlanQty < CompletedQuantity */
			System.Data.DataTable dtbActualTable;
			dtbActualTable = dstMAIN.Tables[ACTUAL_TABLE_NAME];				

			System.Data.DataTable dtbPlanTable;
			dtbPlanTable  = dstMAIN.Tables[PLAN_TABLE_NAME_2];		
			// Modify the PLAN TABLE - get the real PlanDay (depend on Working Time of active working day)
			dtbPlanTable = ModifyPlanTable(dtbPlanTable, pstrCCNID, pstrYear, pstrMonth, pstrProductionLineID);
			//dtbPlanTable = LookUpWithActualTable(dtbPlanTable, dtbActualTable);
			dtbPlanTable = SumAndGroupBy(dtbPlanTable, PRODUCTID, PLAN + DATE, PLAN + QUANTITY);
			
			System.Data.DataTable dtbPlanTableWO1;			
			dtbPlanTableWO1  = dstMAIN.Tables[PLAN_TABLE_NAME_1];
			// Modify the PLAN TABLE - get the real PlanDay (depend on Working Time of active working day)
			dtbPlanTableWO1 = ModifyPlanTable(dtbPlanTableWO1, pstrCCNID, pstrYear, pstrMonth, pstrProductionLineID);
			dtbPlanTableWO1 = LookUpWithActualTable(dtbPlanTableWO1, dtbActualTable);
			dtbPlanTableWO1 = SumAndGroupBy(dtbPlanTableWO1, PRODUCTID, PLAN + DATE, PLAN + QUANTITY);
			
			System.Data.DataTable dtbAdjTable;
			dtbAdjTable = BuildAdjTable(dtbPlanTableWO1 , dtbPlanTable);
			if(nVersion_1 == nVersion_2)	// short circuit, make faster when 2 version is the same, all adjust field is null (zer0)
			{
				dtbAdjTable = new DataTable(ADJ_TABLE_NAME);
				dtbAdjTable.Columns.Add(PRODUCTID);
				dtbAdjTable.Columns.Add(ADJ + DATE, typeof(Int32) );
				dtbAdjTable.Columns.Add(ADJ + QUANTITY, typeof(Decimal) );
			}
			dtbAdjTable.TableName = ADJ_TABLE_NAME;
			dstMAIN.Tables.Add(dtbAdjTable);

            /* finish using ActualTable to modify the 2 PlanTables, now, we group by and SUM the ActualQuantity to display on the report as normal */
			dtbActualTable = SumAndGroupBy(dtbActualTable , PRODUCTID, ACTUAL + DATE, ACTUAL + QUANTITY);

			System.Data.DataTable dtbSOTable;
			dtbSOTable  = dstMAIN.Tables[SO_TABLE_NAME];

			System.Data.DataTable dtbWOBOMTable;
			dtbWOBOMTable = dstMAIN.Tables[WOBOM_TABLE_NAME];

			System.Data.DataTable dtbBEGINTable;
			dtbBEGINTable = dstMAIN.Tables[BEGINQUANTITY_TABLE_NAME];						
				
			//System.Data.DataTable dtbReturnTable;
			//dtbReturnTable = dstMAIN.Tables[RETURN_TO_VENDOR_TABLE_NAME];
		
			#endregion  My Variables

			#region	GETTING THE PARAMETER
			PCSComUtils.Common.BO.UtilsBO boUtil = new PCSComUtils.Common.BO.UtilsBO();
			PCSComUtils.Framework.ReportFrame.BO.C1PrintPreviewDialogBO objBO = new PCSComUtils.Framework.ReportFrame.BO.C1PrintPreviewDialogBO();
			strReportParameter_CCN = boUtil.GetCCNCodeFromID(nCCNID);	
			strReportParameter_ProductionLine = objBO.GetProductLineCodeFromID(nProductionLineID) + ": " + objBO.GetProductLineNameFromID(nProductionLineID);
			
			#endregion	
			
			/// transform TABLE column names
			/// transform TABLE will contain :
			/// PRODUCTID, 
			/// META INFO  = CATEGORY,PARTNO,MODEL,
			/// BEGIN QUANTITY
			/// PLAN+i.ToString("00")
			/// ADJ +i.ToString("00")
			/// ACTUAL+i.ToString("00")
			/// RETURN+i.ToString("00")
			/// ProgressDay, Progress, Assessment
			#region TRANSFORM ORIGINAL TABLE FOR REPORT		
	
			#region GETTING THE DATE HEADING
			/// arrPlanDate and arrActualDate contain DateTime object from actual dtbSourceData
			ArrayList arrPlanDate = GetColumnValuesFromTable(dtbPlanTable,PLAN+DATE);
			arrPlanDate = GetColumnValuesFromTable(dtbSOTable,SO+DATE, arrPlanDate );
			arrPlanDate = GetColumnValuesFromTable(dtbWOBOMTable,WOBOM+DATE, arrPlanDate);

			ArrayList arrActualDate = GetColumnValuesFromTable(dtbActualTable,ACTUAL+DATE);
			//ArrayList arrReturnDate = GetColumnValuesFromTable(dtbReturnTable,RETURN+DATE);			
			ArrayList arrAdjDate = GetColumnValuesFromTable(dtbAdjTable,ADJ+DATE);

			//ArrayList arrItems = GetCategory_PartNo_Model_ProductID_FromTable(dtbPlanTable,CATEGORY,PARTNO,MODEL,PRODUCTID);
			ArrayList arrItems = GetColumnValuesFromTable(dtbPlanTable, PRODUCTID);

			/// PUSH: has-value (in the dtbSourceData) to the arrHasValueDateHeading
			/// 
			/// HACKED: Thachnn: 20/12/2005
			/// don't remove this dummy code of casting object in arrPlanDate to int
			/// because sometime, data in the database is not correct, return dbnull to the arrPlanDate. If we use normal foreach(int nDay in arrPlanDate)
			/// exception of Invalid cast will be throw
			/// In this case: ActualDate can be omit and = DBNull because an Item can be Plan, but it didn't produce in any day in this month
			foreach(object obj  in arrPlanDate)
			{
				try
				{
					int nDay = (int)obj;
					DateTime dtm = new DateTime(nYear,nMonth,nDay);
					string strColumnName = PLAN + dtm.Day.ToString("00");					
					arrHasValueDateHeading.Add(strColumnName);
				}
				catch{}
			}
			
			foreach(object obj in arrActualDate)
			{
				try
				{
					int nDay = (int)obj;
					DateTime dtm = new DateTime(nYear,nMonth,nDay);
					string strColumnName = ACTUAL + dtm.Day.ToString("00");					
					arrHasValueDateHeading.Add(strColumnName);
				}
				catch{}
			}
			
			foreach(object obj in arrAdjDate)
			{
				try
				{
					int nDay = (int)obj;
					DateTime dtm = new DateTime(nYear,nMonth,nDay);
					string strColumnName = ADJ + dtm.Day.ToString("00");					
					arrHasValueDateHeading.Add(strColumnName);
				}
				catch{}
			}
			/// ENDHACKED: Thachnn: 20/12/2005
			/// after this snip of code. arrHasValueDateHeading will contain Actual01, Actual02 or Plan03 Plan04, or Adj03 Adj04, Return02, Return03  ... 
			/// depend on the DataTable
			/// Which day has value (Plan , Adj, or Actual,Return), the columnName will exist in the arrHasValueDateHeading
			/// and then, the Transform DataTable dtbTransform will has some columns named like string in arrHasValueDateHeading			

			#endregion		
            			
			DataTable dtbTransform = BuildTransformTable(arrHasValueDateHeading);
		
			#endregion  TRANSFORM ORIGINAL TABLE FOR REPORT
						
			#region FILL ABSOLUTE DATA FROM Plan && Actual && Adjust && Return to the TRANSFORM DATATABLE
			
			/// GUIDE: with each Items
			foreach(object obj /* ProductID */  in arrItems)
			{
				string strItem = obj.ToString();
				// Create DUMMYROW FIRST
				DataRow dtrNew = dtbTransform.NewRow();

				#region	- fill ITEM meta info to the new dummy row				
				
				string strFilterMeta = string.Empty;
				
				strFilterMeta = string.Format("[{0}]='{1}' ",		
					PRODUCTID,	strItem);

				/// GUIDE: get all rows of this Item from the dtbSourceData
				DataRow[] dtrows = dtbMetaTable.Select(strFilterMeta);

				/// GUIDE: for each rows in result (datarow contain map ProductID -- MetaInfo)
				foreach(DataRow dtr in dtrows)
				{
					// fill data to the dummy row
					dtrNew[PRODUCTID] = dtr[PRODUCTID];
					dtrNew[CATEGORY] = dtr[CATEGORY];
					dtrNew[PARTNO] = dtr[PARTNO];
					dtrNew[PARTNAME] = dtr[PARTNAME];
					dtrNew[MODEL] = dtr[MODEL];
					// TODO: Thachnn: maybe we will ad Begin Quantity to the MetaInfo Table @!!!   dtrNew[BEGIN] = dtr[BEGIN];
				}

				#endregion	- fill ITEM meta info to the new dummy row
			
				#region	- fill PLAN quantity to the new dummy row				
								
				string strFilterPlan = string.Empty;
				
				strFilterPlan = 
					string.Format("[{0}]='{1}' ",					
					PRODUCTID,	strItem	);			
				
				/// GUIDE: get all rows of this Item from the dtbPlan
				DataRow[] dtrowsPlan = dtbPlanTable.Select(strFilterPlan);

				/// GUIDE: for each rows in of this Item OF dtbPlan - fill plan quantity ITEM
				foreach(DataRow dtr in dtrowsPlan)
				{					
					/// Fill Plan Quantity to destination column of Transform table, in this new rows					
					string strDateColumnToFill = PLAN + Convert.ToInt32( dtr[PLAN+DATE]).ToString("00");
					dtrNew[strDateColumnToFill] = dtr[PLAN+QUANTITY];				
				}

				#endregion - fill PLAN quantity to the new dummy row
				
				#region	- fill SO quantity to the new dummy row				
								
				string strFilterSO = string.Empty;
				
				strFilterSO = 
					string.Format("[{0}]='{1}' ",					
					PRODUCTID,	strItem	);			
				
				/// GUIDE: get all rows of this Item from the dtbSO
				DataRow[] dtrowsSO = dtbSOTable.Select(strFilterSO);

				/// GUIDE: for each rows in of this Item OF dtbSO - fill SO quantity ITEM
				foreach(DataRow dtr in dtrowsSO)
				{
					/// ADD SO Quantity to PLAN COLUMN
					string strDateColumnToFill = PLAN + Convert.ToInt32( dtr[SO+DATE]).ToString("00");
					dtrNew[strDateColumnToFill] = ReportBuilder.ToDecimal( dtrNew[strDateColumnToFill])  + ReportBuilder.ToDecimal( dtr[SO+QUANTITY]) ;
				}

				#endregion - fill SO quantity to the new dummy row

				#region	- fill WOBOM quantity to the new dummy row
								
				string strFilterWOBOM = string.Empty;
				
				strFilterWOBOM = 
					string.Format("[{0}]='{1}' ",					
					PRODUCTID,	strItem	);
				
				/// GUIDE: get all rows of this Item from the dtbWOBOM
				DataRow[] dtrowsWOBOM = dtbWOBOMTable.Select(strFilterWOBOM);

				/// GUIDE: for each rows in of this Item OF dtbWOBOM - fill WOBOM quantity ITEM
				foreach(DataRow dtr in dtrowsWOBOM)
				{
					/// ADD WOBOM Quantity to PLAN COLUMN
					string strDateColumnToFill = PLAN + Convert.ToInt32( dtr[WOBOM+DATE]).ToString("00");
					dtrNew[strDateColumnToFill] = ReportBuilder.ToDecimal( dtrNew[strDateColumnToFill])  + ReportBuilder.ToDecimal( dtr[WOBOM+QUANTITY]) ;
				}

				#endregion - fill WOBOM quantity to the new dummy row

				#region - fill BEGIN quantity to the new dummy row
				
				string strFilterBEGIN = string.Empty;
				strFilterBEGIN = 
					string.Format("[{0}]='{1}' ",
					PRODUCTID,	strItem	);		
				
				/// GUIDE: get all rows of this Item from the dtbSourceData
				DataRow[] dtrowsBEGIN = dtbBEGINTable.Select(strFilterBEGIN);
				decimal decBegin = 0;
				try
				{
					decBegin = Convert.ToDecimal(dtrowsBEGIN[0][BEGIN]);
				}
				catch{}
				if (nMonth == 1 || nMonth == 7)
					decBegin = 0;
				dtrNew[BEGIN] = decBegin;

				#endregion - fill BEGIN  quantity to the new dummy row

				#region - fill ACTUAL quantity to the new dummy row
				
				string strFilterActual = string.Empty;
				strFilterActual = 
					string.Format("[{0}]='{1}' ",
					PRODUCTID,	strItem	);		
				
				/// GUIDE: get all rows of this Item from the dtbSourceData
				DataRow[] dtrowsActual = dtbActualTable.Select(strFilterActual);

				/// GUIDE: for each rows  of this Item in Actual Completion DataTable- fill actual quantity to the dummy ROW
				foreach(DataRow dtr in dtrowsActual)
				{
					/// Fill Actual Quantity to destination column of Transform table, in this new rows
					//strDateColumnToFill = ACTUAL + ((DateTime)dtr[ACTUAL+DATE]).Day.ToString("00");
					string strDateColumnToFill = ACTUAL + Convert.ToInt32( dtr[ACTUAL+DATE]).ToString("00");
					dtrNew[strDateColumnToFill] = dtr[ACTUAL+QUANTITY];
				}
				#endregion - fill ACTUAL  quantity to the new dummy row

				#region - fill ADJUST quantity to the new dummy row
				
				/// so we put IsNull in the filter string (to select from dtbResult);
				string strFilterAdjust = string.Empty;
				strFilterAdjust = 
					string.Format("[{0}]='{1}' ",
					PRODUCTID,	strItem		);		
				
				/// GUIDE: get all rows of this Item from the dtbSourceData
				DataRow[] dtrowsAdjust = dtbAdjTable.Select(strFilterAdjust);

				/// GUIDE: for each rows  of this Item in Adjust DataTable- fill actual quantity to the dummy ROW
				foreach(DataRow dtr in dtrowsAdjust)
				{
					/// Fill Actual Quantity to destination column of Transform table, in this new rows
					//strDateColumnToFill = ADJ + ((DateTime)dtr[ADJ +DATE]).Day.ToString("00");
					string strDateColumnToFill = ADJ + Convert.ToInt32( dtr[ADJ+DATE]).ToString("00");
					dtrNew[strDateColumnToFill] = dtr[ADJ+QUANTITY];
				}
				#endregion - fill ADJUST quantity to the new dummy row
	
				#region - fill RETURN quantity to the new dummy row

				//				/// if strItem.Split('#')[0] ==  string.empty, its mean Category value is null
				//				/// so we put IsNull in the filter string (to select from dtbResult);
				//				string strFilterReturn = string.Empty;
				//				strFilterReturn = 
				//					string.Format("[{0}]='{1}' ",
				//					PRODUCTID,
				//					strItem.Split('#')[3]
				//					);		
				//				
				//				/// GUIDE: get all rows of this Item from the dtbSourceData
				//				DataRow[] dtrowsReturn = dtbReturnTable.Select(strFilterReturn);
				//
				//				/// GUIDE: for each rows  of this Item in Return DataTable- fill return quantity to the dummy ROW
				//				foreach(DataRow dtr in dtrowsReturn)
				//				{
				//					/// Fill Return Quantity to destination column of Transform table, in this new rows
				//					//strDateColumnToFill = RETURN + ((DateTime)dtr[RETURN+DATE]).Day.ToString("00");
				//					string strDateColumnToFill = RETURN + Convert.ToInt32( dtr[RETURN+DATE]).ToString("00");
				//					dtrNew[strDateColumnToFill] = dtr[RETURN+QUANTITY];
				//				}
				#endregion - fill RETURN quantity to the new dummy row


				// add to the transform data table
				dtbTransform.Rows.Add(dtrNew);				
			}	    
			#endregion FILL DATA FROM Plan DTB && ActualCompletion DTB && Adjust DTB to the TRANSFORM DATATABLE

			#region CALCULATE the Sum of Plan, sum of Actual, sum of Progress (on top of the report) to generate a chart in EXCEL			
			double[,] arrSumPlan = new double[1,31];
			double[,] arrSumActual = new double[1,31];
			double[,] arrSumProgress = new double[1,31]; 
			
			for(int i = 1 ; i <= DateTime.DaysInMonth(nYear,nMonth)  ; i++)
			{
				string strCounter = i.ToString("00");

				/// sum on the top of the report, calculate to put in the excel file to generate a chart.
				//string str = "Sum(Plan"+i.ToString("00")+")";
				try
				{
					arrSumPlan[0,i-1] = double.Parse(dtbTransform.Compute("Sum(Plan"+i.ToString("00")+")" , string.Empty ).ToString());
				}
				catch{}
				
				try
				{
					arrSumActual[0,i-1] = double.Parse(dtbTransform.Compute("Sum(Actual"+i.ToString("00")+")" , string.Empty).ToString());
				}
				catch{}

				/// progress SUm will be caculate in the next section , after render the report, we will get the real value of upper sum field on report
				/// because the progress value is caculate on render time, depend on the real actual data on the rendered report				
			}		// end foreach Day(i)
			#endregion calculate the Sum of Plan, sum of Actual, sum of Progress (on top of the report) to generate a chart in EXCEL

			#region CALCULATE the ProgressDay column

			for(int i = 1 ; i <= DateTime.DaysInMonth(nYear,nMonth); i++)
			{
				string strCounter = i.ToString(ReportBuilder.FORMAT_DAY_2CHAR);				
				foreach(DataRow rowItem in dtbTransform.Rows)
				{
					decimal decPlan = ReportBuilder.ToDecimal(rowItem[PLAN+strCounter]);
					decimal decActual = ReportBuilder.ToDecimal(rowItem[ACTUAL+strCounter]);
					//					decimal decReturn = ReportBuilder.ToDecimal(rowItem[RETURN+strCounter]);					

					rowItem[PROGRESSDAY+strCounter] = decActual - decPlan; // - decReturn;
				}			
			}	

			#endregion calculate the ProgressDay column

			#region CALCULATE , fill Progress quantity to the new dummy row

			for(int i = 1 ; i <= 31 /*DateTime.DaysInMonth(nYear,nMonth)*/  ; i++)
			{
				foreach(object obj  in arrItems)
				{
					string strItem = obj.ToString();					
					string strFilterProgress = 
						string.Format("[{0}]='{1}' ",
						PRODUCTID,	strItem	);
				
					/// GUIDE: get rows ( in fact, it is only one) of this Item from the dtbTransform
					DataRow[] dtrowsItemAllInfo = dtbTransform.Select(strFilterProgress);
			
					decimal decCurrentACTUAL = ReportBuilder.ToDecimal( dtrowsItemAllInfo[0][ACTUAL+ i.ToString("00")] );
					decimal decCurrentPLAN = ReportBuilder.ToDecimal( dtrowsItemAllInfo[0][PLAN+ i.ToString("00")]) ;
					//					decimal decCurrentRETURN = ReportBuilder.ToDecimal( dtrowsItemAllInfo[0][RETURN+ i.ToString("00")]) ;
					
					decimal decPreviousPROGRESS = decimal.Zero;
					if(i == 1)
						decPreviousPROGRESS = ReportBuilder.ToDecimal( dtrowsItemAllInfo[0][BEGIN] );
					else					
						decPreviousPROGRESS = ReportBuilder.ToDecimal( dtrowsItemAllInfo[0][PROGRESS + (i-1).ToString("00")]  ) /*Previous*/ ;					

					dtrowsItemAllInfo[0][PROGRESS + i.ToString("00")] = 
						decPreviousPROGRESS
						+decCurrentACTUAL
						-decCurrentPLAN;
					//						-decCurrentRETURN;
					
				}	// end each Items (of current day  = i)
			}		// end foreach Day(i)
			
			#endregion - calculate , fill Progress quantity to the new dummy row			
			
			// keep sum of whole report PASS or FAIL
			int intTotalCountPass = 0;			int intTotalCountFail = 0;						
			#region ASSESS the PROGRESS, fill ASSESSMENT and CALCULATE the count of FAIL and PASS
			for(int i = 1 ; i <= DateTime.DaysInMonth(nYear,nMonth); i++)
			{
				int intColumnPass = 0;			int intColumnFail = 0;
				string strCounter = i.ToString(ReportBuilder.FORMAT_DAY_2CHAR);		
				
				foreach(DataRow rowItem in dtbTransform.Rows)
				{					
					decimal decPlan = ReportBuilder.ToDecimal(rowItem[PLAN+strCounter]);
					decimal decActual = ReportBuilder.ToDecimal(rowItem[ACTUAL+strCounter]);
					//					decimal decReturn = ReportBuilder.ToDecimal(rowItem[RETURN+strCounter]);
					decimal decProgressDay = decActual - decPlan; // - decReturn;
					decimal decProgress = ReportBuilder.ToDecimal(rowItem[PROGRESS+strCounter]);

					if (decPlan == decimal.Zero  && decActual == decimal.Zero )
					{ /* Ignore, don't assess the progress */ }
					else
					{
						if (decProgressDay == decimal.Zero)
						{
							rowItem[ASSESSMENT+strCounter] = ASSESSMENT_OK;
							intTotalCountPass ++;	// total
							rowItem[ROWCOUNTPASS] = ReportBuilder.ToInt32(rowItem[ROWCOUNTPASS]) + 1;
							intColumnPass++;							
						}
						else if ((decProgressDay > 0) && (decProgress <= 0))
						{
							rowItem[ASSESSMENT+strCounter] = ASSESSMENT_OK;
							intTotalCountPass ++;
							rowItem[ROWCOUNTPASS] = ReportBuilder.ToInt32(rowItem[ROWCOUNTPASS]) + 1;
							intColumnPass++;
						}
						else
						{
							rowItem[ASSESSMENT+strCounter] = ASSESSMENT_NG;
							intTotalCountFail ++;
							rowItem[ROWCOUNTFAIL] = ReportBuilder.ToInt32(rowItem[ROWCOUNTFAIL]) + 1;
							intColumnFail++;
						}
					}										
				}	// end each rowItem in Transform table				
				arrColumnPass.Add(FLD + PLANPASS + strCounter, intColumnPass);
				arrColumnFail.Add  (FLD + PLANFAIL  + strCounter, intColumnFail );
			}	// end foreach i			

			#endregion calculate the count of Plan FAIL and Plan PASS			

			#region CALCULATE the Percent (column in dtbTransform) for each Row
			foreach(DataRow rowItem in dtbTransform.Rows)
			{
				int nSum = ReportBuilder.ToInt32(rowItem[ROWCOUNTPASS]) + ReportBuilder.ToInt32(rowItem[ROWCOUNTFAIL]);
				if( nSum != 0 )
				{
					double dblPercent = (double)ReportBuilder.ToInt32(rowItem[ROWCOUNTPASS]) / nSum;
					rowItem[ROWPERCENT] = (dblPercent*100).ToString("#0.00") + "%";
				}					
			}
			#endregion CALCULATE the Percent for each Row

			#region  SHORT CIRCURT this function (uncomment these line for the summary report: ProductionLineAssessment)
			//			if((intTotalCountPass + intTotalCountFail) == 0 )
			//			{
			//				mResult = "-";
			//			}
			//			else
			//			{
			//				decimal decTemp  = ( (decimal)intTotalCountPass * 100) / (intTotalCountPass + intTotalCountFail) ;
			//				mResult = decTemp.ToString("#,##0.00");
			//			}
			//			return dtbTransform;
			#endregion SHORT CIRCURT this function

			#region RENDER REPORT
			
			ReportBuilder objRB = mReportBuilder; //new ReportBuilder();
			objRB.ReportName = REPORT_NAME;
			objRB.SourceDataTable = dtbTransform;
			
			#region INIT REPORT BUIDER OBJECT
			try
			{
				if (mstrReportLayoutFile == null || mstrReportLayoutFile == string.Empty)
					mstrReportLayoutFile = "ProductionLineProductionProgressReport.xml";
				objRB.ReportDefinitionFolder = mstrReportDefinitionFolder;
				objRB.ReportLayoutFile = mstrReportLayoutFile;
				if(objRB.AnalyseLayoutFile() == false)
				{
					//					PCSMessageBox.Show(ErrorCode.MESSAGE_REPORT_TEMPLATE_FILE_NOT_FOUND, MessageBoxIcon.Error);
					return new DataTable();
				}
				//objRB.UseLayoutFile = objRB.AnalyseLayoutFile();	// use layout file if any , auto drawing if not found layout file
				objRB.UseLayoutFile = true;	// always use layout file
			}
			catch
			{
				objRB.UseLayoutFile = false;
				//				PCSMessageBox.Show(ErrorCode.MESSAGE_REPORT_TEMPLATE_FILE_NOT_FOUND,MessageBoxIcon.Error);
			}

			C1.C1Report.Layout objLayout = objRB.Report.Layout;
			#endregion				
		
			objRB.MakeDataTableForRender();
				
			// and show it in preview dialog				
			PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog printPreview = new PCSUtils.Framework.ReportFrame.C1PrintPreviewDialog();				
			printPreview.FormTitle = REPORT_NAME;
			objRB.ReportViewer = printPreview.ReportViewer;			
			objRB.RenderReport();			


			if(dtbPlanTable.Rows.Count > 0)
			{	
				for(int i = 0 ; i <= DateTime.DaysInMonth(nYear,nMonth) ;  )
				{				
					try
					{
						arrSumProgress[0,i] = (double)objRB.GetFieldByName(FLD + "Sum" + PROGRESS + (++i).ToString("00")).Value;
					}
					catch{}
				}

				#region BUILD CHART, save to image in clipboard, and then put in the report field fldChart		
				
			

				Field fldChart = objRB.GetFieldByName(REPORTFLD_CHART);
				Field fldTotalChart = objRB.GetFieldByName(REPORTFLD_TOTALCHART);
			
				#region	INIT CHART WITH EXCEL

				//				string EXCEL_REPORT_FOLDER = "ExcelReport";			
				string EXCEL_FILE = "ProductionLineProductionProgressReport.xls";
			
				//				if( ! Directory.Exists(APP_PATH + Path.DirectorySeparatorChar + 	EXCEL_REPORT_FOLDER) )
				//				{
				//					Directory.CreateDirectory(APP_PATH + Path.DirectorySeparatorChar + 	EXCEL_REPORT_FOLDER);
				//				}

				//				string strTemplateFilePath = APP_PATH + Path.DirectorySeparatorChar + 
				//					REPORT_DEFINITION_FOLDER + Path.DirectorySeparatorChar + 
				//					EXCEL_FILE;
				string strTemplateFilePath = mstrReportDefinitionFolder + Path.DirectorySeparatorChar + EXCEL_FILE;

				//				string strDestinationFilePath = APP_PATH + Path.DirectorySeparatorChar + 
				//					EXCEL_REPORT_FOLDER + Path.DirectorySeparatorChar + 
				//					Path.GetFileNameWithoutExtension(EXCEL_FILE) + FormControlComponents.NowToUTCString() + ".XLS";

				string strDestinationFilePath = mstrReportDefinitionFolder + Path.DirectorySeparatorChar + 
					Path.GetFileNameWithoutExtension(EXCEL_FILE) + FormControlComponents.NowToUTCString() + ".XLS";

				/// Copy layout excel report file to ExcelReport folder with a UTC datetime name
				File.Copy(strTemplateFilePath,	strDestinationFilePath,	true);


				PCSUtils.Utils.ExcelReportBuilder objXLS = new ExcelReportBuilder(strDestinationFilePath);
			
			
				#endregion

				try
				{            		        
					#region BUILD THE REPORT ON EXCEL FILE
                
					objXLS.GetRange("A1", "AE1").Value2 = arrSumPlan;
					objXLS.GetRange("A2", "AE2").Value2 = arrSumActual;
					objXLS.GetRange("A3", "AE3").Value2 = arrSumProgress;

					DateTime dtmForExcel = new DateTime(nYear,nMonth,1);
					string[] arrExcelColumnHeading = new string[DateTime.DaysInMonth(nYear,nMonth)];
					for(int i = 1; i <= DateTime.DaysInMonth(nYear,nMonth) ; i++ )
					{						
						arrExcelColumnHeading[i-1] = i.ToString(ReportBuilder.FORMAT_DAY_2CHAR) +ReportBuilder.SEPERATOR_DATETIME + dtmForExcel.ToString(ReportBuilder.FORMAT_MONTH_3CHAR)  /* + ReportBuilder.SEPERATOR_DATETIME */ +"\n" + dtmForExcel.ToString("ddd");
						dtmForExcel = dtmForExcel.AddDays(1);
					}
					objXLS.GetRange("A4","AE4").Value2 = arrExcelColumnHeading;

					// DONE: Replace Proportion Standard with input Parameter
					objXLS.GetRange("ProportionStandard",Type.Missing).Value2		= dblProportionStandard;
					objXLS.GetRange("ProportionAchievePlan",Type.Missing ).Value2	= objRB.GetFieldByName("fldAchievePercent");

					Excel.ChartObject chart = objXLS.GetChart("DetailChart");
					/// PLAN
					((Excel.Series)chart.Chart.SeriesCollection(1)).Values =  objXLS.GetRange( objXLS.GetCell(1,1),objXLS.GetCell(1, DateTime.DaysInMonth(nYear,nMonth)   ) );
					/// ACTUAL
					((Excel.Series)chart.Chart.SeriesCollection(2)).Values =  objXLS.GetRange( objXLS.GetCell(2,1),objXLS.GetCell(2, DateTime.DaysInMonth(nYear,nMonth)   ) );
					/// PROGRESS
					((Excel.Series)chart.Chart.SeriesCollection(3)).Values =  objXLS.GetRange( objXLS.GetCell(3,1),objXLS.GetCell(3, DateTime.DaysInMonth(nYear,nMonth)   ) );


					chart.Chart.CopyPicture(Excel.XlPictureAppearance.xlScreen,Excel.XlCopyPictureFormat.xlBitmap,
						Excel.XlPictureAppearance.xlScreen);
					Image image =  (Image)Clipboard.GetDataObject().GetData(typeof(Bitmap));
					/// DEBUG: to view the chart export to image
					/// image.Save("c:\\"+EXCEL_FILE+".chartExcel.Emf",System.Drawing.Imaging.ImageFormat.Emf);
					fldChart.Visible = true;
					fldChart.Text = "";
					fldChart.Picture = image;

					chart = objXLS.GetChart("TotalChart");
					chart.Chart.CopyPicture(Excel.XlPictureAppearance.xlScreen,Excel.XlCopyPictureFormat.xlBitmap,
						Excel.XlPictureAppearance.xlScreen);
					image =  (Image)Clipboard.GetDataObject().GetData(typeof(Bitmap));
					/// DEBUG: to view the chart export to image
					/// image.Save("c:\\"+EXCEL_FILE+".chartExcel.Emf",System.Drawing.Imaging.ImageFormat.Emf);
					fldTotalChart.Visible = true;
					fldTotalChart.Text = "";
					fldTotalChart.Picture = image;

					#endregion
			
				}
				catch(Exception ex)
				{		
					/// Test: remove if needed
					/// MessageBox.Show("Can not inter operate with Excel: " + ex.Message,"Production Control System",MessageBoxButtons.OK,MessageBoxIcon.Error);
				}
				finally
				{			
					#region SAVE, CLOSE EXCEL FILE CONTAIN REPORT
					objXLS.CloseWorkbook();		
					objXLS.Dispose();
					objXLS = null;
					#endregion
				}				

				#endregion BUILD CHART		

			}
			
			#region MODIFY THE REPORT LAYOUT - do not modify data value on report from now on

			#region PUSH PARAMETER VALUE
			objRB.DrawPredefinedField(REPORTFLD_PARAMETER_CCN,strReportParameter_CCN);			
			objRB.DrawPredefinedField(REPORTFLD_PARAMETER_MONTH, pstrMonth);
			objRB.DrawPredefinedField(REPORTFLD_PARAMETER_YEAR, pstrYear);			
			objRB.DrawPredefinedField(REPORTFLD_PARAMETER_ELEMENT, strReportParameter_ProductionLine);			
			objRB.DrawPredefinedField(REPORTFLD_PARAMETER_VERSION1, strReportParameter_Version1);
			objRB.DrawPredefinedField(REPORTFLD_PARAMETER_VERSION2, strReportParameter_Version2);
			string strProportionStandard = dblProportionStandard.ToString() ;
			objRB.DrawPredefinedField(REPORTFLD_PROPORTIONSTANDARDPERCENT, strProportionStandard );
			#endregion		
			

			#region RENAME THE COLUMN HEADING TEXT
			for(int i = 0; i <= 31; i++) /// clear the heading text
			{
				objRB.DrawPredefinedField(PREFIX_DAYINMONTH+i.ToString(ReportBuilder.FORMAT_DAY_2CHAR),"");
			}
			objRB.DrawPredefinedList_DaysOfWeek( nYear, nMonth,
				PREFIX_DAYINMONTH,
				PREFIX_DAYOFWEEK,
				1 ,DateTime.DaysInMonth(nYear , nMonth ) );

			#endregion

			ArrayList arrResult = objBO.GetOffDayOfProductionLineInMonth(pstrCCNID,pstrYear,pstrMonth,pstrProductionLineID );
			objRB.DrawPredefinedList_MarkOffDayInMonth(arrResult, nYear, nMonth,
				PREFIX_DAYINMONTH,
				PREFIX_DAYOFWEEK,
				1 ,DateTime.DaysInMonth(nYear , nMonth ) );

			
			/// PUSH THE COUNT OF COLUMN COUNT FAIL AND COLUMN COUNT PASS
			foreach(DictionaryEntry objEntry in arrColumnPass)
			{				
				objRB.DrawPredefinedField(objEntry.Key.ToString()  ,objEntry.Value.ToString());				
			}
			foreach(DictionaryEntry objEntry in arrColumnFail)
			{				
				objRB.DrawPredefinedField(objEntry.Key.ToString()  ,objEntry.Value.ToString() );				
			}
			objRB.DrawPredefinedField(REPORTFLD_TOTALPASS , intTotalCountPass.ToString() );
			objRB.DrawPredefinedField(REPORTFLD_TOTALFAIL  , intTotalCountFail.ToString()   );

			#region HIDE the column of not-existed day in current month
			// 1. IN1T :: what to clear
			string[] arrFieldToClear = {
										   FLD + "Sum" + PLAN,
										   FLD + "Sum" + ACTUAL,	// Sum Return is not existed here
										   FLD + "Sum" + "ProgressAccumulate",			
										   PREFIX_DAYINMONTH,	/*// also hide the Day Heading */
										   PREFIX_DAYOFWEEK,
										   FLD + PLAN,
										   FLD + ADJ,
										   FLD + ACTUAL,
										   //										   FLD + RETURN,
										   FLD + PROGRESSDAY,
										   FLD + PROGRESS,
										   FLD + ASSESSMENT,
			
										   FLD + PLANFAIL,
										   FLD + PLANPASS,
										   FLD + "PlanTotalPercentD"				
									   };		// contain name of field need to clear if day column is not exist
            
			objRB.HideColumnNotExistInMonth(nYear,nMonth, arrFieldToClear);

			#endregion HIDE the column of not-existed day in current month

			StringCollection arrFieldNames = new StringCollection();
			arrFieldNames.AddRange(arrFieldToClear);			

			string LEFT_ANCHOR_FLD = PREFIX_DAYINMONTH + "01";
			string LEFT_MARGIN_FLD = "lblLeftMarginToSpread";
			string RIGHT_MARGIN_FLD = "lblRightMarginToSpread";

			double dblWidthToSpead = objRB.ActualPageWidth - 
				(objRB.GetFieldByName(LEFT_MARGIN_FLD).Width + 	objRB.GetFieldByName(RIGHT_MARGIN_FLD).Width	) ;
			objRB.SpreadColumnsWithinWidth(arrFieldNames, 1,  DateTime.DaysInMonth(nYear,nMonth),
				objRB.GetFieldByName(LEFT_ANCHOR_FLD).Left, dblWidthToSpead  );

			objRB.MarkRedToNegativeNumberField();

			#endregion MODIFY THE REPORT LAYOUT - do not modify data value on report from now on
						
			objRB.RefreshReport();


			/// force the copies number
			printPreview.FormTitle = objRB.GetFieldByName(REPORTFLD_TITLE).Text;			
			printPreview.Show();
			#endregion
			
			UseReportViewerRenderEngine = false;
			
			return dtbTransform;
		}
		

		/// <summary>
		/// Thachnn : 15/Oct/2005
		/// Browse the DataTable, get all value of column with provided named.
		/// </summary>
		/// <param name="pdtb">DataTable to collect values</param>
		/// <param name="pstrColumnName">COlumn Name in pdtb DataTable to collect values from</param>
		/// <returns>ArrayList of object, collect from pdtb's column named pstrColumnName. Empty ArrayList if error or not found any row in pdtb.</returns>
		private static ArrayList GetColumnValuesFromTable(DataTable pdtb, string pstrColumnName)
		{
			ArrayList arrRet = new ArrayList();
			try
			{
				foreach (DataRow drow in pdtb.Rows)
				{
					object objGet = drow[pstrColumnName];
					if( !arrRet.Contains(objGet)  )
					{
						arrRet.Add(objGet);
					}
				}
			}
			catch
			{
				arrRet.Clear();
			}
			return arrRet;
		}

		/// <summary>
		/// Thachnn : 15/Oct/2005
		/// Browse the DataTable, get all value of column with provided named.
		/// </summary>
		/// <param name="pdtb">DataTable to collect values</param>
		/// <param name="pstrColumnName">COlumn Name in pdtb DataTable to collect values from</param>
		/// <returns>ArrayList of object, collect from pdtb's column named pstrColumnName. Empty ArrayList if error or not found any row in pdtb.</returns>
		private static ArrayList GetColumnValuesFromTable(DataTable pdtb, string pstrColumnName, ArrayList arrOriginal)
		{
			ArrayList arrRet = arrOriginal;
			try
			{
				foreach (DataRow drow in pdtb.Rows)
				{
					object objGet = drow[pstrColumnName];
					if( !arrRet.Contains(objGet)  )
					{
						arrRet.Add(objGet);
					}
				}
			}
			catch
			{
				// arrRet.Clear();
			}
			return arrRet;
		}

		/// <summary>
		/// build a new datatable with column = productid, category,partno,model,begin,
		/// and somecolumn with names in arrHasValueDateHeading
		/// Index column is : Plan, Adj, Actual, Return, ProgressDay, Progress Accumulate, Assessment
		/// 
		/// </summary>
		/// <remarks>		
		/// </remarks>
		/// <returns>DataTable</returns>
		private DataTable BuildTransformTable(ArrayList parrHasValueDateHeading)
		{
			DataTable dtbRet = new DataTable(TABLE_NAME);
			dtbRet.Columns.Add(PRODUCTID,typeof(System.Int32) );
			dtbRet.Columns.Add(CATEGORY,typeof(System.String) );
			dtbRet.Columns.Add(PARTNO,typeof(System.String));
			dtbRet.Columns.Add(PARTNAME,typeof(System.String));
			dtbRet.Columns.Add(MODEL,typeof(System.String));
			dtbRet.Columns.Add(BEGIN,typeof(System.Double));
			dtbRet.Columns.Add(ROWCOUNTPASS,typeof(System.Int32));
			dtbRet.Columns.Add(ROWCOUNTFAIL,typeof(System.Int32));
			dtbRet.Columns.Add(ROWPERCENT,typeof(System.String));

			/// fill the column (Double type) in which the date exist in the dtbSourceData (has value contain in the parrDueDateHeading)
			/// then fill the column with String type (so that it will display correctly in the report, not #,##0.00, because it has null value)
					
			foreach(string strColumnName in parrHasValueDateHeading)
			{					
				try
				{
					dtbRet.Columns.Add(strColumnName,typeof(System.Double));
				}
				catch{}
			}
			// FILL the null column				
			for(int i = 1; i <=31; i++)												  
			{
				if(parrHasValueDateHeading.Contains(PLAN + i.ToString("00")) == false )
				{		
					try
					{
						dtbRet.Columns.Add(PLAN + i.ToString("00"),typeof(System.String));						
					}
					catch{}
				}
				if(parrHasValueDateHeading.Contains(ADJ + i.ToString("00")) == false )
				{		
					try
					{
						dtbRet.Columns.Add(ADJ + i.ToString("00"),typeof(System.String));
					}
					catch{}
				}
				if(parrHasValueDateHeading.Contains(ACTUAL + i.ToString("00")) == false )
				{		
					try
					{
						dtbRet.Columns.Add(ACTUAL + i.ToString("00"),typeof(System.String));
					}
					catch{}
				}

				//				if(parrHasValueDateHeading.Contains(RETURN + i.ToString("00")) == false )
				//				{		
				//					try
				//					{
				//						dtbRet.Columns.Add(RETURN + i.ToString("00"),typeof(System.String));
				//					}
				//					catch{}
				//				}
				try
				{
					dtbRet.Columns.Add(PROGRESSDAY + i.ToString("00"),typeof(System.Double));
				}
				catch{}
				try
				{
					dtbRet.Columns.Add(PROGRESS + i.ToString("00"),typeof(System.Double));
				}
				catch{}

				try
				{
					dtbRet.Columns.Add(ASSESSMENT + i.ToString("00"),typeof(System.String));
				}
				catch{}

			} 	// FILL the null column
			
			return dtbRet;		
		}	// end build transform tables

		/// <summary>
		/// Thachnn : 08/Nov/2005
		/// Browse the DataTable, get all value of Category, PartNo column, insert into ArraysList as CategoryValue#PartNoValue
		/// </summary>
		/// <param name="pdtb">DataTable to collect values</param>			
		/// <param name="pstrCategoryColName"></param>
		/// <param name="pstrPartNoColName"></param>
		/// <returns>ArrayList of object, collect CategoryValue#PartNoValue pairs from pdtb. Empty ArrayList if error or not found any row in pdtb.</returns>		
		private ArrayList GetCategory_PartNo_Model_ProductID_FromTable(DataTable pdtb, string pstrCategoryColName, string pstrPartNoColName, string pstrModelColName, string pstrProductID)
		{
			ArrayList arrRet = new ArrayList();
			try
			{
				foreach (DataRow drow in pdtb.Rows)
				{
					object objCategoryGet = drow[pstrCategoryColName];
					object objPartNoGet = drow[pstrPartNoColName];
					object objModelGet = drow[pstrModelColName];
					object objProductIDGet = drow[pstrProductID];
					string str = objCategoryGet.ToString().Trim() + "#" + objPartNoGet.ToString().Trim() + "#" + objModelGet.ToString().Trim() + "#" + objProductIDGet.ToString().Trim();
					if( !arrRet.Contains(str)  )
					{
						arrRet.Add(str);
					}
				}
			}
			catch
			{
				arrRet.Clear();
			}
			return arrRet;
		}

		/// <summary>
		/// Thachnn: 10/11/2005
		/// execute the input sql clause
		/// return the object result
		/// throw all exception to outside
		/// </summary>
		/// <param name="pstrSql">SQL clause to execute</param>
		/// <returns>object</returns>
		public object ExecuteScalar(string pstrSql)
		{
			
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;

			try
			{
				oconPCS = new OleDbConnection(mConnectionString);
				ocmdPCS = new OleDbCommand(pstrSql, oconPCS);

				ocmdPCS.Connection.Open();
				return ocmdPCS.ExecuteScalar();
			}
			finally
			{
				if (oconPCS != null)
					if (oconPCS.State != ConnectionState.Closed)
						oconPCS.Close();
			}
		}

		/// <summary>
		/// <author>Thachnn</author>
		/// Get the datatable: 
		/// contain Mapping ProductID with it adjustment from TABLE1 to TABLE2, in a specific days in month)
		/// Return table will have schema like:
		/// ProductID - AdjDay - AdjQuantity
		/// 200	1	110.00000
		/// 200	2	10.00000		
		/// 127	13	53.00000
		/// 127	31	50.00000		
		/// </summary>
		/// <returns></returns>
		private DataTable BuildAdjTable(DataTable pdtbTable1, DataTable pdtbTable2 )
		{	
			/// TABLE RET = TABLE1 - TABLE2

			/// build schema for ADJ table			
			DataTable dtbRet = new DataTable(ADJ_TABLE_NAME);
			dtbRet.Columns.Add(PRODUCTID);
			dtbRet.Columns.Add(ADJ + DATE, typeof(Int32) );
			dtbRet.Columns.Add(ADJ + QUANTITY, typeof(Decimal) );
			
			// using inner datatable.  not modify the original input parameter (Table1 and Table2)
			DataTable dtb1 = pdtbTable1.Copy();
			DataTable dtb2 = pdtbTable2.Copy();

			/// FOREACH iROW IN TABLE2, 
			/// dtbRet newROW = iROW(2)
			/// if found relative-newROW    row in Table1 (productid is the same, planday is the same), 
			/// ---- subtract PlanQuantity of current row (newROW).
			/// ---- Add found row in Table1 in the UsedRowedInTableWO1
			/// add newROW to dtbRET
			/// 
			/// Table1.Remove(UsedRowedInTableWO1);
			/// 
			/// FOREACH iROW1 in TABLE1
			/// CLone, Negate and ADD to the dtbRet
			

            
			if(dtb1.Rows.Count == 0)
			{
				return dtbRet;
			}			

			ArrayList arrUsedRowInTable1 = new ArrayList();
			int nPID = int.MinValue;
			int nDay = int.MinValue;			
			foreach(DataRow iRow in dtb2.Rows)
			{
				DataRow newRow = dtbRet.NewRow();
				nPID = System.Convert.ToInt32(iRow[PRODUCTID]);
				nDay = System.Convert.ToInt32(iRow[PLAN + DATE]);
				decimal dblAQ = Convert.ToDecimal( iRow[PLAN + QUANTITY]  );

				decimal dblRelativeFromTable1 = 0;
				foreach( DataRow jRow in dtb1.Select("[PRODUCTID]=" +nPID+ " and [PLANDAY]=" +nDay)   )
				{
					dblRelativeFromTable1 += Convert.ToDecimal( jRow[PLAN + QUANTITY]) ;
					arrUsedRowInTable1.Add(jRow);	/// mark that we used this rows. We don't include its value later.
				}

				newRow[PRODUCTID] = nPID;
				newRow[ADJ + DATE] = nDay;
				newRow[ADJ + QUANTITY] = dblAQ - dblRelativeFromTable1;
				dtbRet.Rows.Add(newRow);
			}

			foreach(DataRow jRow in arrUsedRowInTable1)
			{
				try
				{
					dtb1.Rows.Remove(jRow);			
				}
				catch{}				
			}
            
			foreach(DataRow iRow in dtb1.Rows)
			{
				DataRow newRow = dtbRet.NewRow();
				newRow[PRODUCTID] = iRow[PRODUCTID];
				newRow[ADJ + DATE] = iRow[PLAN + DATE];
				try
				{
					
					newRow[ADJ + QUANTITY] = decimal.Negate( (decimal)iRow[PLAN + QUANTITY] );
				}
				catch{}				

				dtbRet.Rows.Add(newRow);
			}

			return dtbRet;
		}

		/// <summary>
		/// Get all data for this report and cache in the dstMAIN dataset
		/// just improve the speed for this report
		/// </summary>
		/// <param name="pstrCCNID"></param>
		/// <param name="pstrYear"></param>
		/// <param name="pstrMonth"></param>
		/// <param name="pstrProductionLineID"></param>
		/// <param name="pstrProportionStandard"></param>
		private DataSet GetDataAndCache(string pstrCCNID, string pstrYear, string pstrMonth, string pstrProductionLineID, string pstrVersion_1, string pstrVersion_2, string pstrProportionStandard)
		{	
			DataSet dstRET = new DataSet();
			OleDbConnection oconPCS = null;
			OleDbCommand ocmdPCS = null;
			int nYear = Convert.ToInt32(pstrYear);
			int nMonth = Convert.ToInt32(pstrMonth);
			DateTime dtmCurrentMonth = new DateTime(nYear, nMonth, 1);
			DateTime dtmPreviousMonth = dtmCurrentMonth.AddMonths(-1);
			DateTime dtmBeginDate;
			DateTime dtmEndDate = dtmCurrentMonth.AddSeconds(-1);
			if (dtmPreviousMonth.Month >= 7)
				dtmBeginDate = new DateTime(dtmPreviousMonth.Year, 7, 1);
			else
				dtmBeginDate = new DateTime(dtmPreviousMonth.Year, 1, 1);

			#region MAIN SQL QUERY
				
			string strSql = 
				" Declare @pstrCCNID int " + 
				" Declare @pstrMonth char(2) " + 
				" Declare @pstrYear char(4) " + 
				" Declare @pstrPreviousMonth char(2) " + 
				" Declare @pstrPreviousYear char(4) " + 
				" Declare @BeginDate datetime " + 
				" Declare @EndDate datetime " + 
				" Declare @pstrProductionLineID int " + 
				" Declare @pstrVersion_1 char(9) " + 			
				" Declare @pstrVersion_2 char(9) " + 			
				"  " + 				
				" /*-----------------------------------*/ " + 
				"  " + 
				" Set @pstrCCNID = " + pstrCCNID + " " + 
				" Set @pstrYear = '" + pstrYear + "' " + 
				" Set @pstrMonth = '"+ pstrMonth +"' " + 
				" Set @BeginDate = '"+ dtmBeginDate.ToString("yyyy-MM-dd") +"' " + 
				" Set @EndDate = '"+ dtmEndDate.ToString("yyyy-MM-dd") +"' " + 
				" Set @pstrPreviousYear = '" + dtmPreviousMonth.Year + "' " + 
				" Set @pstrPreviousMonth = '"+ dtmPreviousMonth.Month +"' " + 
				" Set @pstrProductionLineID = " +pstrProductionLineID+ " " + 

				(pstrVersion_1.Trim() == string.Empty ?  (string.Empty)        :  (" Set @pstrVersion_1 = '" +pstrVersion_1 + "' ")  )  + 
				(pstrVersion_2.Trim() == string.Empty ?  (string.Empty)       :  (" Set @pstrVersion_2 = '" +pstrVersion_2 + "' ")  )  +  
				"  " + 					
				"  " ;
			/*-----------------------------------*/

			#endregion MAIN QUERY

			#region META _DATA

			string strSql_META_TABLE =

				" select distinct  " + 
				" ITEM.ProductID as [ProductID] , " + 
				" ITEM.Code as [PartNo], " + 
				" ITEM.Description as [PartName], " + 
				" CAT.Code as [Category] , " + 
				" ITEM.Revision as [Model] " + 
				"  " + 
				" FROM PRO_DCPResultMaster as WOMASTER " + 
				" join PRO_DCPResultDetail as WODETAIL " + 
				" on WOMASTER.DCPResultMasterID = WODETAIL.DCPResultMasterID " + 
				" join MST_WorkCenter ON WOMASTER.WorkCenterID = MST_WorkCenter.WorkCenterID" + 
				" AND ISNULL(IsMain,0) = 1" + 
				" AND MST_WorkCenter.ProductionLineID = @pstrProductionLineID" + 
				" join ITM_Product ITEM " + 
				" on WOMASTER.ProductID = ITEM.ProductID " + 
				" left join ITM_Category as CAT " + 
				" on ITEM.CategoryID = CAT.CategoryID " + 
				"  " + 
				" where DatePart(mm  ,WorkingDate) = @pstrMonth " + 
				" and DatePart(yyyy ,WorkingDate) = @pstrYear " + 
				"  " ; 
			#endregion META _DATA

			#region PLANTABLE - 2 - MAIN
	
			/// Get the datatable : contain Mapping ProductID with it Plan from WO2, in a specific days in month)
			/// Return table will have schema like:
			/// ProductID - PlanDay - PlanQuantity - WorkOrderDetailID
			/// 200	1	110.00000
			/// 200	2	10.00000					
			/// 127	31	50.00000		
			/// TODO: need to group by to make PID and PlanDay is unique key
			/// //private DataTable BuildPlanTable(string pstrCCNID, string pstrYear, string pstrMonth, string pstrProductionLineID, string pstrWorkOrderMasterID)
			string strSqlPLANTABLE_2 =	/* newer */
		
				/*************************GENERAL PLAN TABLE *******************************************************************************/
				" SELECT  " + 
				" PLANTABLE.ProductID, " + 
				" PLANTABLE.PlanDate, " + 
				" PLANTABLE.PlanQuantity " + 
				"  " + 
				"  " + 
				" FROM " + 
				" ( " + 
				" 	/*************************PLAN TABLE *******************************************************************************/ " + 
				" 	select  " + 
				" 	WOMASTER.ProductID as [ProductID],  " + 
				" 	WODETAIL.WorkingDate as [PlanDate],  " + 
				" 	SUM(IsNull(WODETAIL.Quantity, 0.00)) as [PlanQuantity],  " + 
				" 	( " + 
				" 	ltrim(str(DatePart(yyyy,PlanningPeriod))) " + 
				" 	+ " + 
				" 	( " + 
				" 		CASE len(ltrim(str(DatePart(mm,PlanningPeriod))))  " + 
				" 		when 1 then '0' + ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 		else ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 		end " + 
				" 	) " + 
				" 	+ " + 
				" 	( " + 
				" 		CASE len(ltrim(str(Version)))  " + 
				" 		when 1 then '00' + ltrim(str(Version)) " + 
				" 		when 2 then '0' + ltrim(str(Version)) " + 
				" 		else ltrim(str(DatePart(mm,Version))) " + 
				" 		end " + 
				" 	) " + 
				" 	) as Version " + 
				" 	 " + 
				" 	from " + 
				" 	PRO_DCPResultDetail as WODETAIL " + 
				" 	join PRO_DCPResultMaster WOMASTER " + 
				" 	on WODETAIL.DCPResultMasterID = WOMASTER.DCPResultMasterID " + 
				" 		and DatePart(mm   , WODETAIL.WorkingDate) = @pstrMonth " + 
				" 		and DatePart(yyyy , WODETAIL.WorkingDate) = @pstrYear " + 
				" 	JOIN MST_WorkCenter MWC ON WOMASTER.WorkCenterID = MWC.WorkCenterID " + 
				" 	AND ISNULL(MWC.IsMain,0) = 1 " + 
				" 	AND MWC.ProductionLineID = @pstrProductionLineID " + 
				" 	 " + 
				" 	join PRO_DCOptionMaster DCOMASTER " + 
				" 	on WOMASTER.DCOptionMasterID = DCOMASTER.DCOptionMasterID " + 
				" 	and " + 
				" 	( " + 
				" 	ltrim(str(DatePart(yyyy,PlanningPeriod))) " + 
				" 	+ " + 
				" 	( " + 
				" 		CASE len(ltrim(str(DatePart(mm,PlanningPeriod))))  " + 
				" 		when 1 then '0' + ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 		else ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 		end " + 
				" 	) " + 
				" 	+ " + 
				" 	( " + 
				" 		CASE len(ltrim(str(Version)))  " + 
				" 		when 1 then '00' + ltrim(str(Version)) " + 
				" 		when 2 then '0' + ltrim(str(Version)) " + 
				" 		else ltrim(str(DatePart(mm,Version))) " + 
				" 		end " + 
				" 	) " + 
				" 	) " + 
				" 	<= @pstrVersion_2 " + 
				"  " + 
				" 	and DCOMASTER.CCNID = @pstrCCNID		 " + 
				" 	/* Take all the relate to Parameter Year-month period of DCO.FromDate (AsOfDate) < first day of NextMonth. EndDate (AsOfDate + PlanHorizon) >= first day of CurrentProvidedMonth */ " + 
				" 	and /*FromDate*/ DCOMASTER.AsOfDate < dateadd (month, 1, convert(datetime, @pstrYear + '-' + @pstrMonth + '-' + '01' ) ) /*beginning of next month*/ " + 
				" 	and /*Begin of current month*/ convert(datetime, @pstrYear + '-' + @pstrMonth + '-' + '01' ) <= DATEADD(day, DCOMASTER.PlanHorizon, DCOMASTER.AsOfDate) /*EndDate*/ " + 
				" 	 " + 
				" 	group by  " + 
				" 	WOMASTER.ProductID, " + 
				" 	WODETAIL.WorkingDate, " + 
				" 	DCOMASTER.Version, " + 
				" 	DCOMASTER.PlanningPeriod " + 
				" 	 " + 
				" 	/*************************PLAN TABLE *******************************************************************************/ " + 
				" ) as PLANTABLE " + 
				" join " + 
				" ( " + 
				" 	select " + 
				" 	PLANTABLE.ProductID as [ProductID], " + 
				" 	DatePart(dd,PLANTABLE.PlanDate) as [PlanDateDD], " + 				
				" 	Max(PLANTABLE.Version) as [Version] " + 
				" 	 " + 
				" 	from  " + 
				" 	( " + 
				" 		/*************************PLAN TABLE *******************************************************************************/ " + 
				" 		select  " + 
				" 		WOMASTER.ProductID as [ProductID], " + 
				" 		WODETAIL.WorkingDate as [PlanDate], " + 
				" 		SUM(IsNull(WODETAIL.Quantity, 0.00)) as [PlanQuantity], " + 
				" 		( " + 
				" 		ltrim(str(DatePart(yyyy,PlanningPeriod))) " + 
				" 		+ " + 
				" 		( " + 
				" 			CASE len(ltrim(str(DatePart(mm,PlanningPeriod))))  " + 
				" 			when 1 then '0' + ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 			else ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 			end " + 
				" 		) " + 
				" 		+ " + 
				" 		( " + 
				" 			CASE len(ltrim(str(Version)))  " + 
				" 			when 1 then '00' + ltrim(str(Version)) " + 
				" 			when 2 then '0' + ltrim(str(Version)) " + 
				" 			else ltrim(str(DatePart(mm,Version))) " + 
				" 			end " + 
				" 		) " + 
				" 		) as Version " + 
				" 		 " + 
				" 		from " + 
				" 		PRO_DCPResultDetail as WODETAIL " + 
				" 		join PRO_DCPResultMaster WOMASTER " + 
				" 		on WODETAIL.DCPResultMasterID = WOMASTER.DCPResultMasterID " + 
				" 		and DatePart(mm   , WODETAIL.WorkingDate) = @pstrMonth " + 
				" 		and DatePart(yyyy , WODETAIL.WorkingDate) = @pstrYear " + 
				" 	JOIN MST_WorkCenter MWC ON WOMASTER.WorkCenterID = MWC.WorkCenterID " + 
				" 	AND ISNULL(MWC.IsMain,0) = 1 " + 
				" 	AND MWC.ProductionLineID = @pstrProductionLineID " + 
				" 		 " + 
				" 		join PRO_DCOptionMaster DCOMASTER " + 
				" 		on WOMASTER.DCOptionMasterID = DCOMASTER.DCOptionMasterID " + 
				" 		and " + 
				" 		( " + 
				" 		ltrim(str(DatePart(yyyy,PlanningPeriod))) " + 
				" 		+ " + 
				" 		( " + 
				" 			CASE len(ltrim(str(DatePart(mm,PlanningPeriod))))  " + 
				" 			when 1 then '0' + ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 			else ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 			end " + 
				" 		) " + 
				" 		+ " + 
				" 		( " + 
				" 			CASE len(ltrim(str(Version)))  " + 
				" 			when 1 then '00' + ltrim(str(Version)) " + 
				" 			when 2 then '0' + ltrim(str(Version)) " + 
				" 			else ltrim(str(DatePart(mm,Version))) " + 
				" 			end " + 
				" 		) " + 
				" 		) " + 
				" 		<= @pstrVersion_2 " + 
				"  " + 
				"  " + 
				" 		and DCOMASTER.CCNID = @pstrCCNID		 " + 
				" 		/* Take all the relate to Parameter Year-month period of DCO.FromDate (AsOfDate) < first day of NextMonth. EndDate (AsOfDate + PlanHorizon) >= first day of CurrentProvidedMonth */ " + 
				" 		and /*FromDate*/ DCOMASTER.AsOfDate < dateadd (month, 1, convert(datetime, @pstrYear + '-' + @pstrMonth + '-' + '01' ) ) /*beginning of next month*/ " + 
				" 		and /*Begin of current month*/ convert(datetime, @pstrYear + '-' + @pstrMonth + '-' + '01' ) <= DATEADD(day, DCOMASTER.PlanHorizon, DCOMASTER.AsOfDate) /*EndDate*/ " + 
				"  " + 
				" 		group by  " + 
				" 		WOMASTER.ProductID,  " + 
				" 		WODETAIL.WorkingDate,  " + 
				" 		DCOMASTER.Version,  " + 
				" 		DCOMASTER.PlanningPeriod  " + 
				" 		 " + 
				" 		/*************************PLAN TABLE *******************************************************************************/ " + 
				" 	) as PLANTABLE " + 
				" 	group by  " + 
				" 	PLANTABLE.ProductID, " + 
				" 	DatePart(dd,PLANTABLE.PlanDate) " + 
				
				" ) as MAXVERSIONTABLE " + 
				"  " + 
				" on PLANTABLE.ProductID = MAXVERSIONTABLE.ProductID " + 
				" and DatePart(dd,PLANTABLE.PlanDate) = MAXVERSIONTABLE.PlanDateDD " + 
				" and PLANTABLE.Version = MAXVERSIONTABLE.Version " + 				
				"  " + 
				"  " 
				/*************************GENERAL PLAN TABLE *******************************************************************************/		
				;

			#endregion PLANTABLE - MAIN
			/* ============================================================== */

			#region PLANTABLE - 1 - NOT MANDATORY - similar to PLANTABLE 2, except , version = Version_1
	
			string strSqlPLANTABLE_1 = 
				/*************************GENERAL PLAN TABLE *******************************************************************************/
				" SELECT  " + 
				" PLANTABLE.ProductID, " + 
				" PLANTABLE.PlanDate, " + 
				" PLANTABLE.PlanQuantity " + 
				"  " + 
				"  " + 
				" FROM " + 
				" ( " + 
				" 	/*************************PLAN TABLE *******************************************************************************/ " + 
				" 	select  " + 
				" 	WOMASTER.ProductID as [ProductID],  " + 
				" 	WODETAIL.WorkingDate as [PlanDate],  " + 
				" 	SUM(IsNull(WODETAIL.Quantity, 0.00)) as [PlanQuantity],  " + 
				" 	( " + 
				" 	ltrim(str(DatePart(yyyy,PlanningPeriod))) " + 
				" 	+ " + 
				" 	( " + 
				" 		CASE len(ltrim(str(DatePart(mm,PlanningPeriod))))  " + 
				" 		when 1 then '0' + ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 		else ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 		end " + 
				" 	) " + 
				" 	+ " + 
				" 	( " + 
				" 		CASE len(ltrim(str(Version)))  " + 
				" 		when 1 then '00' + ltrim(str(Version)) " + 
				" 		when 2 then '0' + ltrim(str(Version)) " + 
				" 		else ltrim(str(DatePart(mm,Version))) " + 
				" 		end " + 
				" 	) " + 
				" 	) as Version " + 
				" 	 " + 
				" 	from " + 
				" 	PRO_DCPResultDetail as WODETAIL " + 
				" 	join PRO_DCPResultMaster WOMASTER " + 
				" 	on WODETAIL.DCPResultMasterID = WOMASTER.DCPResultMasterID " + 
				" 	and DatePart(mm   , WODETAIL.WorkingDate) = @pstrMonth " + 
				" 	and DatePart(yyyy , WODETAIL.WorkingDate) = @pstrYear " + 
				" 	JOIN MST_WorkCenter MWC ON WOMASTER.WorkCenterID = MWC.WorkCenterID " + 
				" 	AND ISNULL(MWC.IsMain,0) = 1 " + 
				" 	AND MWC.ProductionLineID = @pstrProductionLineID " + 
				" 	 " + 
				" 	join PRO_DCOptionMaster DCOMASTER " + 
				" 	on WOMASTER.DCOptionMasterID = DCOMASTER.DCOptionMasterID " + 
				" 	and " + 
				" 	( " + 
				" 	ltrim(str(DatePart(yyyy,PlanningPeriod))) " + 
				" 	+ " + 
				" 	( " + 
				" 		CASE len(ltrim(str(DatePart(mm,PlanningPeriod))))  " + 
				" 		when 1 then '0' + ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 		else ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 		end " + 
				" 	) " + 
				" 	+ " + 
				" 	( " + 
				" 		CASE len(ltrim(str(Version)))  " + 
				" 		when 1 then '00' + ltrim(str(Version)) " + 
				" 		when 2 then '0' + ltrim(str(Version)) " + 
				" 		else ltrim(str(DatePart(mm,Version))) " + 
				" 		end " + 
				" 	) " + 
				" 	) " + 
				" 	<= @pstrVersion_1 " + 
				"  " + 
				" 	and DCOMASTER.CCNID = @pstrCCNID		 " + 
				" 	/* Take all the relate to Parameter Year-month period of DCO.FromDate (AsOfDate) < first day of NextMonth. EndDate (AsOfDate + PlanHorizon) >= first day of CurrentProvidedMonth */ " + 
				" 	and /*FromDate*/ DCOMASTER.AsOfDate < dateadd (month, 1, convert(datetime, @pstrYear + '-' + @pstrMonth + '-' + '01' ) ) /*beginning of next month*/ " + 
				" 	and /*Begin of current month*/ convert(datetime, @pstrYear + '-' + @pstrMonth + '-' + '01' ) <= DATEADD(day, DCOMASTER.PlanHorizon, DCOMASTER.AsOfDate) /*EndDate*/ " + 
				" 	 " + 
				" 	group by  " + 
				" 	WOMASTER.ProductID, " + 
				" 	WODETAIL.WorkingDate, " + 
				" 	DCOMASTER.Version, " + 
				" 	DCOMASTER.PlanningPeriod " + 
				" 	 " + 
				" 	/*************************PLAN TABLE *******************************************************************************/ " + 
				" ) as PLANTABLE " + 
				" join " + 
				" ( " + 
				" 	select " + 
				" 	PLANTABLE.ProductID as [ProductID], " + 
				" 	DatePart(dd,PLANTABLE.PlanDate) as [PlanDateDD], " + 				
				" 	Max(PLANTABLE.Version) as [Version] " + 
				" 	 " + 
				" 	from  " + 
				" 	( " + 
				" 		/*************************PLAN TABLE *******************************************************************************/ " + 
				" 		select  " + 
				" 		WOMASTER.ProductID as [ProductID], " + 
				" 		WODETAIL.WorkingDate as [PlanDate], " + 
				" 		SUM(IsNull(WODETAIL.Quantity, 0.00)) as [PlanQuantity], " + 
				" 		( " + 
				" 		ltrim(str(DatePart(yyyy,PlanningPeriod))) " + 
				" 		+ " + 
				" 		( " + 
				" 			CASE len(ltrim(str(DatePart(mm,PlanningPeriod))))  " + 
				" 			when 1 then '0' + ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 			else ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 			end " + 
				" 		) " + 
				" 		+ " + 
				" 		( " + 
				" 			CASE len(ltrim(str(Version)))  " + 
				" 			when 1 then '00' + ltrim(str(Version)) " + 
				" 			when 2 then '0' + ltrim(str(Version)) " + 
				" 			else ltrim(str(DatePart(mm,Version))) " + 
				" 			end " + 
				" 		) " + 
				" 		) as Version " + 
				" 		 " + 
				" 		from " + 
				" 		PRO_DCPResultDetail as WODETAIL " + 
				" 		join PRO_DCPResultMaster WOMASTER " + 
				" 		on WODETAIL.DCPResultMasterID = WOMASTER.DCPResultMasterID " + 
				" 		and DatePart(mm   , WODETAIL.WorkingDate) = @pstrMonth " + 
				" 		and DatePart(yyyy , WODETAIL.WorkingDate) = @pstrYear " + 
				" 	JOIN MST_WorkCenter MWC ON WOMASTER.WorkCenterID = MWC.WorkCenterID " + 
				" 	AND ISNULL(MWC.IsMain,0) = 1 " + 
				" 	AND MWC.ProductionLineID = @pstrProductionLineID " + 
				" 		 " + 
				" 		join PRO_DCOptionMaster DCOMASTER " + 
				" 		on WOMASTER.DCOptionMasterID = DCOMASTER.DCOptionMasterID " + 
				" 		and " + 
				" 		( " + 
				" 		ltrim(str(DatePart(yyyy,PlanningPeriod))) " + 
				" 		+ " + 
				" 		( " + 
				" 			CASE len(ltrim(str(DatePart(mm,PlanningPeriod))))  " + 
				" 			when 1 then '0' + ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 			else ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 			end " + 
				" 		) " + 
				" 		+ " + 
				" 		( " + 
				" 			CASE len(ltrim(str(Version)))  " + 
				" 			when 1 then '00' + ltrim(str(Version)) " + 
				" 			when 2 then '0' + ltrim(str(Version)) " + 
				" 			else ltrim(str(DatePart(mm,Version))) " + 
				" 			end " + 
				" 		) " + 
				" 		) " + 
				" 		<= @pstrVersion_1 " + 
				"  " + 
				"  " + 
				" 		and DCOMASTER.CCNID = @pstrCCNID		 " + 
				" 		/* Take all the relate to Parameter Year-month period of DCO.FromDate (AsOfDate) < first day of NextMonth. EndDate (AsOfDate + PlanHorizon) >= first day of CurrentProvidedMonth */ " + 
				" 		and /*FromDate*/ DCOMASTER.AsOfDate < dateadd (month, 1, convert(datetime, @pstrYear + '-' + @pstrMonth + '-' + '01' ) ) /*beginning of next month*/ " + 
				" 		and /*Begin of current month*/ convert(datetime, @pstrYear + '-' + @pstrMonth + '-' + '01' ) <= DATEADD(day, DCOMASTER.PlanHorizon, DCOMASTER.AsOfDate) /*EndDate*/ " + 
				"  " + 
				" 		group by  " + 
				" 		WOMASTER.ProductID,  " + 
				" 		WODETAIL.WorkingDate,  " + 
				" 		DCOMASTER.Version,  " + 
				" 		DCOMASTER.PlanningPeriod  " + 
				" 		 " + 
				" 		/*************************PLAN TABLE *******************************************************************************/ " + 
				" 	) as PLANTABLE " + 
				" 	group by  " + 
				" 	PLANTABLE.ProductID, " + 
				" 	DatePart(dd,PLANTABLE.PlanDate) " + 
				
				" ) as MAXVERSIONTABLE " + 
				"  " + 
				" on PLANTABLE.ProductID = MAXVERSIONTABLE.ProductID " + 
				" and DatePart(dd,PLANTABLE.PlanDate) = MAXVERSIONTABLE.PlanDateDD " + 
				" and PLANTABLE.Version = MAXVERSIONTABLE.Version " + 				
				"  " + 
				"  " 
				/*************************GENERAL PLAN TABLE *******************************************************************************/		
				;

			#endregion PLANTABLE - 1 - NOT MANDATORY - Take all WorkOrder in month_Yearm CCN, where Version = Version_1
			/* ============================================================== */
		
			#region SOTABLE 

			string strSqlSOTABLE = 

				/****************************SO TABLE *********************************************************/
				" select 0 as ProductID, " + 
				" 0 as [SODay], " + 
				" 0.00 as [SOQuantity] " + 
				" where 0>1 "
				/****************************SO TABLE *********************************************************/			
				;

			#endregion SOTABLE (add into the PlanQuantity)

			/* ============================================================== */

			#region WOBOM TABLE

			string strSqlWOBOMTABLE = 
				
				/****************************WOBOM TABLE *********************************************************/
				" SELECT      " + 
				" 0 as [ProductID],  " + 
				" 0 as WOBOMDay, " + 
				" 0.00 AS [WOBOMQuantity]  " + 
				" where 0>1 " 
				/****************************WOBOM TABLE *********************************************************/
				;

			#endregion WOBOM TABLE

			/* ============================================================== */

			#region BEGINQUANTITY TABLE

			string strSqlBEGINQUANTITYTABLE = 				
				

				/************************* PROGRESS BEGIN TABLE ***************************************************************************/
				" /* ============== PROGRESS BEGIN QUANTITY WITH Month = Parameter Month - 1 ================ */ " + 
				" SELECT  " + 
				" IsNull(PREV_PLANTABLE.ProductID,  PREV_ACTUALTABLE.ProductID) as [ProductID], " + 
				" ( IsNull(PREV_ACTUALTABLE.ActualQuantity, 0.00) - IsNull(PREV_PLANTABLE.PlanQuantity, 0.00) )    as [ProgressBeginQuantity]  " + 
				"  " + 
				" FROM " + 
				" (		 " + 
				" 	 " + 
				" 	/*************************GENERAL PLAN TABLE *********** Previous , add sum , and remove the Version constrant ************************/ " + 
				" 	/*************************GENERAL PLAN TABLE *******************************************************************************/ " + 
				" 	SELECT  " + 
				" 	PLANTABLE.ProductID, " + 
				" 	Sum(IsNull(PLANTABLE.PlanQuantity, 0.00)) as [PlanQuantity] " + 
				" 	 " + 
				" 	 " + 
				" 	FROM " + 
				" 	( " + 
				" 		/*************************PLAN TABLE *******************************************************************************/ " + 
				" 		select  " + 
				" 		WOMASTER.ProductID as [ProductID], " + 
				" 		WODETAIL.WorkingDate as [PlanDate], " + 
				" 		SUM(IsNull(WODETAIL.Quantity, 0.00)) as [PlanQuantity], " + 
				" 		( " + 
				" 		ltrim(str(DatePart(yyyy,PlanningPeriod))) " + 
				" 		+ " + 
				" 		( " + 
				" 			CASE len(ltrim(str(DatePart(mm,PlanningPeriod))))  " + 
				" 			when 1 then '0' + ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 			else ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 			end " + 
				" 		) " + 
				" 		+ " + 
				" 		( " + 
				" 			CASE len(ltrim(str(Version)))  " + 
				" 			when 1 then '00' + ltrim(str(Version)) " + 
				" 			when 2 then '0' + ltrim(str(Version)) " + 
				" 			else ltrim(str(DatePart(mm,Version))) " + 
				" 			end " + 
				" 		) " + 
				" 		) as Version " + 
				" 		 " + 
				" 		from " + 
				" 		PRO_DCPResultDetail as WODETAIL " + 
				" 		join PRO_DCPResultMaster WOMASTER " + 
				" 		on WODETAIL.DCPResultMasterID = WOMASTER.DCPResultMasterID " + 
				" 		and WODETAIL.WorkingDate BETWEEN @BeginDate AND @EndDate" + 
				" 	JOIN MST_WorkCenter MWC ON WOMASTER.WorkCenterID = MWC.WorkCenterID " + 
				" 	AND ISNULL(MWC.IsMain,0) = 1 " + 
				" 	AND MWC.ProductionLineID = @pstrProductionLineID " + 
				" 		 " + 
				" 		join PRO_DCOptionMaster DCOMASTER " + 
				" 		on WOMASTER.DCOptionMasterID = DCOMASTER.DCOptionMasterID " + 
				" 		and " + 
				" 		( " + 
				" 		ltrim(str(DatePart(yyyy,PlanningPeriod))) " + 
				" 		+ " + 
				" 		( " + 
				" 			CASE len(ltrim(str(DatePart(mm,PlanningPeriod))))  " + 
				" 			when 1 then '0' + ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 			else ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 			end " + 
				" 		) " + 
				" 		+ " + 
				" 		( " + 
				" 			CASE len(ltrim(str(Version)))  " + 
				" 			when 1 then '00' + ltrim(str(Version)) " + 
				" 			when 2 then '0' + ltrim(str(Version)) " + 
				" 			else ltrim(str(DatePart(mm,Version))) " + 
				" 			end " + 
				" 		) " + 
				" 		) " + 
				" 		<= @pstrVersion_2 " + 
				" 	 " + 
				" 		and DCOMASTER.CCNID = @pstrCCNID		 " + 
				" 		/* Take all the relate to Parameter Year-month period of DCO.FromDate (AsOfDate) < first day of NextMonth. EndDate (AsOfDate + PlanHorizon) >= first day of CurrentProvidedMonth */ " + 
				" 		and /*FromDate*/ DCOMASTER.AsOfDate <  @EndDate/*beginning of next month*/ " + 
				" 		and /*Begin of current month*/ @BeginDate <= DATEADD(day, DCOMASTER.PlanHorizon, DCOMASTER.AsOfDate) /*EndDate*/ " + 
				" 		 " + 
				" 		group by  " + 
				" 		WOMASTER.ProductID, " + 
				" 		WODETAIL.WorkingDate, " + 
				" 		DCOMASTER.Version, " + 
				" 		DCOMASTER.PlanningPeriod " + 
				" 		 " + 
				" 		/*************************PLAN TABLE *******************************************************************************/ " + 
				" 	) as PLANTABLE " + 
				" 	join " + 
				" 	( " + 
				" 		select " + 
				" 		PLANTABLE.ProductID, " + 
				" 		PLANTABLE.PlanDate, " + 
				" 		Max(PLANTABLE.Version) as [Version] " + 
				" 		 " + 
				" 		from  " + 
				" 		( " + 
				" 			/*************************PLAN TABLE *******************************************************************************/ " + 
				" 			select  " + 
				" 			WOMASTER.ProductID as [ProductID], " + 
				" 			WODETAIL.WorkingDate as [PlanDate], " + 
				" 			SUM(IsNull(WODETAIL.Quantity, 0.00)) as [PlanQuantity], " + 
				" 			( " + 
				" 			ltrim(str(DatePart(yyyy,PlanningPeriod))) " + 
				" 			+ " + 
				" 			( " + 
				" 				CASE len(ltrim(str(DatePart(mm,PlanningPeriod))))  " + 
				" 				when 1 then '0' + ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 				else ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 				end " + 
				" 			) " + 
				" 			+ " + 
				" 			( " + 
				" 				CASE len(ltrim(str(Version)))  " + 
				" 				when 1 then '00' + ltrim(str(Version)) " + 
				" 				when 2 then '0' + ltrim(str(Version)) " + 
				" 				else ltrim(str(DatePart(mm,Version))) " + 
				" 				end " + 
				" 			) " + 
				" 			) as Version " + 
				" 			 " + 
				" 			from " + 
				" 		PRO_DCPResultDetail as WODETAIL " + 
				" 		join PRO_DCPResultMaster WOMASTER " + 
				" 		on WODETAIL.DCPResultMasterID = WOMASTER.DCPResultMasterID " + 
				" 		and WODETAIL.WorkingDate BETWEEN @BeginDate AND @EndDate" + 
				" 	JOIN MST_WorkCenter MWC ON WOMASTER.WorkCenterID = MWC.WorkCenterID " + 
				" 	AND ISNULL(MWC.IsMain,0) = 1 " + 
				" 	AND MWC.ProductionLineID = @pstrProductionLineID " + 
				" 			 " + 
				" 			join PRO_DCOptionMaster DCOMASTER " + 
				" 			on WOMASTER.DCOptionMasterID = DCOMASTER.DCOptionMasterID " + 
				" 			and " + 
				" 			( " + 
				" 			ltrim(str(DatePart(yyyy,PlanningPeriod))) " + 
				" 			+ " + 
				" 			( " + 
				" 				CASE len(ltrim(str(DatePart(mm,PlanningPeriod))))  " + 
				" 				when 1 then '0' + ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 				else ltrim(str(DatePart(mm,PlanningPeriod))) " + 
				" 				end " + 
				" 			) " + 
				" 			+ " + 
				" 			( " + 
				" 				CASE len(ltrim(str(Version)))  " + 
				" 				when 1 then '00' + ltrim(str(Version)) " + 
				" 				when 2 then '0' + ltrim(str(Version)) " + 
				" 				else ltrim(str(DatePart(mm,Version))) " + 
				" 				end " + 
				" 			) " + 
				" 			) " + 
				" 			<= @pstrVersion_2 " + 
				" 	 " + 
				" 	 " + 
				" 			and DCOMASTER.CCNID = @pstrCCNID		 " + 
				" 		/* Take all the relate to Parameter Year-month period of DCO.FromDate (AsOfDate) < first day of NextMonth. EndDate (AsOfDate + PlanHorizon) >= first day of CurrentProvidedMonth */ " + 
				" 		and /*FromDate*/ DCOMASTER.AsOfDate <  @EndDate/*beginning of next month*/ " + 
				" 		and /*Begin of current month*/ @BeginDate <= DATEADD(day, DCOMASTER.PlanHorizon, DCOMASTER.AsOfDate) /*EndDate*/ " + 
				" 	 " + 
				" 			group by  " + 
				" 			WOMASTER.ProductID, " + 
				" 			WODETAIL.WorkingDate, " + 
				" 			DCOMASTER.Version, " + 
				" 			DCOMASTER.PlanningPeriod " + 
				" 			 " + 
				" 			/*************************PLAN TABLE *******************************************************************************/ " + 
				" 		) as PLANTABLE " + 
				" 		group by  " + 
				" 		PLANTABLE.ProductID, " + 
				" 		PLANTABLE.PlanDate " + 
				" 	) as MAXVERSIONTABLE " + 
				" 	 " + 
				" 	on PLANTABLE.ProductID = MAXVERSIONTABLE.ProductID " + 
				" 	and PLANTABLE.PlanDate = MAXVERSIONTABLE.PlanDate " + 
				" 	and PLANTABLE.Version = MAXVERSIONTABLE.Version " + 
				" 	 " + 
				" 	GROUP BY " + 
				" 	PLANTABLE.ProductID	/* for general plan sum */ " + 
				" 	/*************************GENERAL PLAN TABLE *******************************************************************************/ " + 
				" 	/*************************GENERAL PLAN TABLE *********** Previous , add sum , set previous and remove the Version constrant ************************/ " + 
				" ) as PREV_PLANTABLE " + 
				"  " + 
				" FULL outer join   " + 
				" (	 " + 
				" 	 " + 
				" 	/************************* ACTUAL TABLE ***************************************************************************/ " + 
				" 	/* get the actual work order completion - map to the ProductID , similar as  Actual Table, remove the ActualDay in the select and group by clause */ " + 
				" 	select " + 
				" 	INNERTABLE.[ProductID], " + 
				" 	SUM(IsNull(INNERTABLE.[CompletedQuantity], 0.00) ) as [ActualQuantity] " + 
				" 	 " + 
				" 	FROM " + 
				" 	( " + 
				" 		select   " + 
				" 		PRO_WorkOrderCompletion.ProductID as [ProductID], " + 
				" 		DATEPART(dd,PRO_WorkOrderCompletion.CompletedDate) as [ActualDay], " + 
				" 		IsNull(PRO_WorkOrderCompletion.CompletedQuantity ,0.00) as [CompletedQuantity] " + 
				" 		 " + 
				" 		from ITM_Product " + 
				" 		join PRO_WorkOrderCompletion " + 
				" 		on ITM_Product.ProductID = PRO_WorkOrderCompletion.ProductID	 " + 
				" 		and ITM_Product.CCNID = @pstrCCNID " + 
				" 		/* NEW USECASE */ " + 
				" 		and PRO_WorkOrderCompletion.LocationID IN (select distinct LocationID from PRO_ProductionLine where PRO_ProductionLine.ProductionLineID = @pstrProductionLineID) " + 
				" 	 " + 
				" 		join PRO_WorkOrderMaster " + 
				" 		on PRO_WorkOrderCompletion.WorkOrderMasterID = PRO_WorkOrderMaster.WorkOrderMasterID " + 
				" 		and PRO_WorkOrderCompletion.CompletedDate BETWEEN @BeginDate AND @EndDate " + 
				" 		and PRO_WorkOrderMaster.ProductionLineID = @pstrProductionLineID		 " + 
				" 	/*	and PRO_WorkOrderMaster.WorkOrderMasterID = @pstrWorkOrderMasterID_2	*/ " + 
				" 	 " + 
				" 	) INNERTABLE " + 
				" 	 " + 
				" 	group by " + 
				" 	INNERTABLE.ProductID " + 
				" 	 " + 
				" 	/************************* ACTUAL TABLE ***************************************************************************/ " + 
				" 	/* get the actual work order completion - map to the ProductID , similar as  Actual Table, remove the ActualDay in the select and group by clause */ " + 
				" 	 " + 
				" ) as PREV_ACTUALTABLE " + 
				"  " + 
				" on PREV_PLANTABLE.ProductID = PREV_ACTUALTABLE.ProductID	 " 

				/************************* PROGRESS BEGIN TABLE ***************************************************************************/

				;

			#endregion BEGINQUANTITY TABLE

			/* ============================================================== */

			#region ACTUALTABLE
			/// Get the datatable : contain Mapping ProductID with it Actual COmpletion quantity, in a specific days in month)
			/// Return table will have schema like:
			/// ProductID - ActualDay - ActualQuantity
			/// 200	1	110.00000			
			/// 200	4	40.00000		
			/// 127	31	50.00000		
			/// </summary>
			/// <returns></returns>
			// private DataTable BuildActualTable(string pstrCCNID, string pstrYear, string pstrMonth, string pstrProductionLineID, string pstrWorkOrderMasterID_2)

			string strSqlACTUALTABLE = 		

				/************************* ACTUAL TABLE ***************************************************************************/
				" /* get the actual work order completion - map to the ProductID */ " + 
				" select " + 
				" INNERTABLE.[ProductID], " + 
				" INNERTABLE.[ActualDay], " + 
				" SUM(IsNull(INNERTABLE.[CompletedQuantity], 0.00) ) as [ActualQuantity], " + 
				" INNERTABLE.WorkOrderDetailStatus as [WorkOrderDetailStatus], " + 
				" INNERTABLE.WorkOrderDetailID as [WorkOrderDetailID] " + 
				"  " + 
				" FROM " + 
				" ( " + 
				" 	select   " + 
				" 	PRO_WorkOrderCompletion.ProductID as [ProductID], " + 
				" 	DATEPART(dd,PRO_WorkOrderCompletion.CompletedDate) as [ActualDay], " + 
				" 	IsNull(PRO_WorkOrderCompletion.CompletedQuantity ,0.00) as [CompletedQuantity],  " + 
				" 	PRO_WorkOrderDetail.Status as [WorkOrderDetailStatus], " + 
				" 	PRO_WorkOrderDetail.WorkOrderDetailID " + 
				" 	 " + 
				" 	from ITM_Product " + 
				" 	join PRO_WorkOrderCompletion " + 
				" 	on ITM_Product.ProductID = PRO_WorkOrderCompletion.ProductID	 " + 
				" 	and ITM_Product.CCNID = @pstrCCNID " + 
				" 	/* NEW USECASE */ " + 
				" 	and PRO_WorkOrderCompletion.LocationID IN (select distinct LocationID from PRO_ProductionLine where PRO_ProductionLine.ProductionLineID = @pstrProductionLineID) " + 
				"  " + 
				" 	join PRO_WorkOrderMaster " + 
				" 	on PRO_WorkOrderCompletion.WorkOrderMasterID = PRO_WorkOrderMaster.WorkOrderMasterID " + 
				" 	and DATEPART(mm  ,PRO_WorkOrderCompletion.CompletedDate) = @pstrMonth " + 
				" 	and DATEPART(yyyy,PRO_WorkOrderCompletion.CompletedDate) = @pstrYear " + 
				" 	and PRO_WorkOrderMaster.ProductionLineID = @pstrProductionLineID		 " + 
				" /*	and PRO_WorkOrderMaster.WorkOrderMasterID = @pstrWorkOrderMasterID_2	*/ " + 
				" 	join PRO_WorkOrderDetail " + 
				" 	on PRO_WorkOrderCompletion.WorkOrderDetailID = PRO_WorkOrderDetail.WorkOrderDetailID " + 
				" 	join MST_Bin " + 
				" 	on PRO_WorkOrderCompletion.BinID = MST_Bin.BinID AND MST_Bin.BinTypeID = 1 " + // OK Bin
				"  " + 
				" ) INNERTABLE " + 
				"  " + 
				" group by " + 
				" INNERTABLE.ProductID, " + 
				" INNERTABLE.ActualDay, " + 
				" INNERTABLE.WorkOrderDetailStatus, " + 
				" INNERTABLE.WorkOrderDetailID " 
				/************************* ACTUAL TABLE ***************************************************************************/
				;

			#endregion ACTUALTABLE

			/* ============================================================== */
				
			try 
			{
				
				oconPCS = null;
				ocmdPCS = null;
			
				strSql += 
					strSql_META_TABLE + "\n" +  
					strSqlPLANTABLE_2 /* newer */ + "\n" +  
					strSqlPLANTABLE_1 + "\n" + 
					strSqlSOTABLE + "\n" +
					strSqlWOBOMTABLE + "\n" +
					strSqlBEGINQUANTITYTABLE + "\n" +
					strSqlACTUALTABLE + "\n" 
					;
	

				oconPCS = new OleDbConnection(mConnectionString);
				ocmdPCS = new OleDbCommand(strSql, oconPCS);
				
				ocmdPCS.Connection.Open();				
				OleDbDataAdapter odadPCS = new OleDbDataAdapter(ocmdPCS);
				odadPCS.Fill(dstRET);

				dstRET.Tables[0].TableName = META_TABLE_NAME;
				dstRET.Tables[1].TableName = PLAN_TABLE_NAME_2;
				dstRET.Tables[2].TableName = PLAN_TABLE_NAME_1;
				dstRET.Tables[3].TableName = SO_TABLE_NAME;
				dstRET.Tables[4].TableName = WOBOM_TABLE_NAME;
				dstRET.Tables[5].TableName = BEGINQUANTITY_TABLE_NAME;
				dstRET.Tables[6].TableName = ACTUAL_TABLE_NAME;

			}
			catch(OleDbException ex)
			{
				throw new Exception(strSql,ex);
			}			
			finally 
			{
				if (oconPCS!=null) 
				{
					if (oconPCS.State != ConnectionState.Closed) 
					{
						oconPCS.Close();
					}
				}
			}
			
			return dstRET;
		}

		/// <summary>
		/// This function return the Maximum DCP Version.
		/// If on Error, or there is no previous, it will return a NEGATIVE value.
		/// </summary>
		/// <param name="pstrCCNID"></param>
		/// <returns></returns>
		private int GetMaxVersion(string pstrCCNID) /* string pstrYear, string pstrMonth, string pstrElementID, */			
		{
			const int NO_VERSION = -1;
			int intRet = NO_VERSION;
			
			#region DB QUERY
				
			string strSql = 
				" Declare @pstrCCNID int " + 				
				" /*-----------------------------------*/ " + 
				" Set @pstrCCNID = " +pstrCCNID+ " " + 								
				" /*-----------------------------------*/ " + 
				"  " + 			
				" select   " + 
				" IsNull( Max(Version) , 0) as MaxVersion " + 
				" from    " + 
				" PRO_DCOptionMaster  as DCOMASTER   	 " + 
				"  " + 
				" where DCOMASTER.CCNID = @pstrCCNID    " ;				

			try
			{
				intRet = Convert.ToInt32( ExecuteScalar(strSql) );
			}
			catch
			{}

			#endregion DB QUERY

			intRet = intRet == 0 ? -1 : intRet;

			return intRet;
		}	// end function

		/// <summary>
		/// This function return the Previous DCP Version of pstrCurrentVersion.
		/// If on Error, or there is no previous, it will return a NEGATIVE value.
		/// </summary>
		/// <param name="pstrCCNID"></param>
		/// <returns></returns>
		private int GetPreviousVersion(string pstrCCNID, /* string pstrYear, string pstrMonth, string pstrElementID, */
			string pstrCurrentVersion)
		{
			const int NO_VERSION = -1;
			int intRet = NO_VERSION;
			
			#region DB QUERY
				
			string strSql = 
				" Declare @pstrCCNID int " + 							
				" Declare @pstrVersion_2 int " + 							
				" /*-----------------------------------*/ " + 
				" Set @pstrCCNID = " +pstrCCNID+ " " + 				
				" Set @pstrVersion = " +pstrCurrentVersion+ " " + 				
				" /*-----------------------------------*/ " + 
				"  " + 			
				" select   " + 
				" IsNull( Max(Version) , 0) as PreviousVersion " + 
				" from    " + 
				" PRO_DCOptionMaster  as DCOMASTER   				 " + 
				"  " + 
				" where DCOMASTER.CCNID = @pstrCCNID    " + 
				" and DCOMASTER.Version < @pstrVersion_2		";			

			try
			{
				intRet = Convert.ToInt32( ExecuteScalar(strSql) );
			}
			catch
			{}

			#endregion DB QUERY

			intRet = intRet == 0 ? NO_VERSION : intRet;

			return intRet;
		}	// end function

		/// <summary>
		/// Modify the original Plantable (WorkOrderTable) to the Real Plan table (which have real "PlanDay", depend on the real working time of day.
		/// (We should remember that, WorkOrder start from 4AM of 12/04/2006 may has real "PlanDay" = 13, because of Shift3 of 11/04/2006 last to 6h14AM of 12/04/2006 )
		/// </summary>
		/// <author>Thachnn</author>
		/// <param name="pdtbOriginalWorkOrderPlan"></param>
		/// <returns></returns>
		private DataTable ModifyPlanTable(DataTable pdtbOriginalWorkOrderPlan, string pstrCCNID, string pstrYear, string pstrMonth, string pstrProductionLineID)
		{
			const string PLANDATE = "PlanDate";

			//DataTable dtbWorkingTime = GetAllPeriodOfWorkingTime(pstrCCNID, pstrYear, pstrMonth, pstrProductionLineID );

			// clone the schema
			DataTable dtbRet = pdtbOriginalWorkOrderPlan.Clone();
			// add new column = PLANDAY
			dtbRet.Columns.Add(PLAN + DATE, typeof(int) );
			// remove the fulltime column PLANDATE (contain yyyy mm dd hh:mm:ss)
			dtbRet.Columns.Remove(PLANDATE);
		
			// foreach row in WorkOrderTable, if PlanDate is in any WorkingTime of any Day, add it in the dtbRet table with that Day value in PlanDay column
			foreach(DataRow drow in pdtbOriginalWorkOrderPlan.Rows)
			{				
				DateTime dtmPlanDateBeforeResolve = DateTime.MinValue;
				if(drow[PLANDATE] != DBNull.Value)
				{
					dtmPlanDateBeforeResolve = DateTime.Parse(drow[PLANDATE].ToString().Trim());
				}

				int nRealWorkingDay = dtmPlanDateBeforeResolve.Day;	//GetRealWorkingDay(dtmPlanDateBeforeResolve, dtbWorkingTime);

				if(nRealWorkingDay  > 0 && nRealWorkingDay <= 31)
				{
					DataRow dtrNew = dtbRet.NewRow();
					dtrNew[PLAN + DATE] = nRealWorkingDay;
					dtrNew[PRODUCTID] = drow[PRODUCTID];
					dtrNew[PLAN + QUANTITY] = drow[PLAN + QUANTITY];
					//dtrNew[WORKORDERDETAILID] = drow[WORKORDERDETAILID];
					dtbRet.Rows.Add(dtrNew);
				}

			}
			
			return dtbRet;
		}

		/// <summary>
		/// Modify the input dtbPlanTable
		/// 
		/// Loop though the PlanTable, for each WorkOrderDetail PlanTable row, select the related actual completed quantity (in the ActualTable).
		/// If related actual row is found, check:
		///		if PlanQuantity less than  ActualQuantity and related actual row has WorkOrderDetailStatus = WORKORDERDETAIL_MFG_CLOSE then:
		///			set PlanQuantity = ActualQuantity
		/// </summary>
		/// <param name="pdtbOriginalPlanTable"></param>
		/// <param name="pdtbActualTable"></param>
		/// <returns></returns>
		private DataTable LookUpWithActualTable(DataTable pdtbOriginalPlanTable, DataTable pdtbActualTable)
		{
			if(pdtbActualTable == null || pdtbActualTable.Rows.Count <= 0)	// nothing to referrence
			{
				return pdtbOriginalPlanTable;
			}

			// temp string to speed up
			string strProductID = string.Empty;
			string strDay = string.Empty;
			//string strWorkOrderDetailID = string.Empty;
			foreach( DataRow drowPlan in pdtbOriginalPlanTable.Rows)
			{				
				strProductID = drowPlan[PRODUCTID].ToString();
				strDay = drowPlan[PLAN + DATE].ToString();
				//strWorkOrderDetailID = drowPlan[WORKORDERDETAILID].ToString();

				string strFilter = string.Empty;
				strFilter = 
					string.Format("[{0}]='{1}' AND [{2}]='{3}'",
					PRODUCTID,		strProductID,
					ACTUAL + DATE,		strDay
					//WORKORDERDETAILID, strWorkOrderDetailID
					);				
				
				DataRow[] dtrowsActual = pdtbActualTable.Select(strFilter);	// always return 1 row if found

				if(dtrowsActual.Length > 0)
				{
					if(  ReportBuilder.ToInt32( dtrowsActual[0][WORKORDERDETAILSTATUS]) == WORKORDERDETAIL_MFG_CLOSE  && 
						ReportBuilder.ToDouble( dtrowsActual[0][ACTUAL + QUANTITY] ) < ReportBuilder.ToDouble( drowPlan[PLAN + QUANTITY] )   
						)
					{
						drowPlan[PLAN + QUANTITY] = dtrowsActual[0][ACTUAL + QUANTITY];
					}	// MFG_CLOSE && end if PlanQuantity < ActualQuantity
				}	// end if found relate Actual Row (with same WorkOrderDetailID , same ProductID, and  in same Day)
			}	// end for each PlanRow in pdtbOriginalPlanTable

            return pdtbOriginalPlanTable;
		}	// end function
	
		/// <summary>
		/// get the reference table for GetRealWorkingDay() function
		/// result is the table with each record contain: 
		/// BeginDate, EndDate (of configured WCCapacity)
		/// WorkTimeFrom, WorkTimeTo	(Real working time of each shift in a working day)
		/// 
		/// SCHEMA: BeginDate, EndDate, WorkTimeFrom, WorkTimeTo
		/// 
		/// </summary>
		/// <author>Thachnn</author>
		/// <param name="pstrCCNID"></param>
		/// <param name="pstrYear"></param>
		/// <param name="pstrMonth"></param>
		/// <param name="pstrProductionLineID"></param>
		/// <returns></returns>
		private DataTable GetAllPeriodOfWorkingTime(string pstrCCNID, string pstrYear, string pstrMonth, string pstrProductionLineID)
		{
			OleDbConnection oconPCS = null;
			OleDbDataAdapter odadPCS = null;
			try
			{
				DataTable dtbData = new DataTable();
				oconPCS = new OleDbConnection(mConnectionString);
				string strSql = 
  
					" Declare @pstrCCNID int " + 
					" Declare @pstrMonth char(2) " + 
					" Declare @pstrYear char(4) " + 
					" Declare @pstrProductionLineID int " + 
					/*-----------------------------------*/
					"  " + 
					" Set @pstrCCNID = " +pstrCCNID+ " " + 
					" Set @pstrYear = '" +pstrYear+ "' " + 
					" Set @pstrMonth = '" +pstrMonth+ "' " + 
					" Set @pstrProductionLineID = " +pstrProductionLineID+ " " + 
					" /*-----------------------------------*/ " + 
					"  " + 
					" 	 " + 

 " select distinct     " + 
 "  " + 
 " WCC.BeginDate,    " + 
 " WCC.EndDate,    " + 
 " SP.WorkTimeFrom,    " + 
 " SP.WorkTimeTo     " + 
 " from     " + 
 " PRO_Shift as S    " + 
 " join PRO_ShiftPattern as SP    " + 
 " 	on S.ShiftID = SP.ShiftID    " + 
 " /*  	and ShiftDesc IN ('1S','2S','3S')   */ /*allow all shift*/ " + 
 " join PRO_ShiftCapacity as SC    " + 
 " 	on S.ShiftID = SC.ShiftID    " + 
 " join PRO_WCCapacity as WCC    " + 
 " 	on WCC.WCCapacityID = SC.WCCapacityID    " + 
 " 	 " + 
 " 	/* Take all the relate to Parameter Year-month period of WCCapacity. BeginDate < first day of NextMonth. EndDate >= first day of CurrentProvidedMonth */ " + 
 " 	and WCC.BeginDate < dateadd (month, 1, convert(datetime, @pstrYear + '-' + @pstrMonth + '-' + '01' ) ) /*beginning of next month*/ " + 
 " 	and convert(datetime, @pstrYear + '-' + @pstrMonth + '-' + '01' ) <= WCC.EndDate  " + 
 "  " + 
 " join MST_WorkCenter as WC    " + 
 " 	on WCC.WorkCenterID = WC.WorkCenterID    " + 
 " 	and WC.ProductionLineID = @pstrProductionLineID    " + 
 " 	and WC.CCNID = @pstrCCNID    " + 
 "  	and WC.IsMain = 1 " +  
 "  " ;
 
				OleDbCommand cmdData = new OleDbCommand(strSql, oconPCS);
				odadPCS = new OleDbDataAdapter(cmdData);
				cmdData.Connection.Open();
				odadPCS.Fill(dtbData);
				return dtbData;
			}
			finally
			{
				if (oconPCS != null)
					if (oconPCS.State != ConnectionState.Closed)
						oconPCS.Close();
			}
		}

		/// <summary>
		/// Put the DateTime need to Resolve in
		/// Reference the WorkingTime table
		/// if the ResolveTime is in the working time of any shift, of a configured period, determine the real WorkingDay, and return.
		/// </summary>
		/// <param name="pdtmNeedToResolve"></param>
		/// <param name="pdtbWorkingTime"></param>
		/// <returns></returns>
		private int GetRealWorkingDay(DateTime pdtmNeedToResolve, DataTable pdtbWorkingTime)
		{
			const string BEGINDATE = "BeginDate";
			const string ENDDATE = "EndDate";
			const string WORKTIMEFROM = "WorkTimeFrom";
			const string WORKTIMETO = "WorkTimeTo";			

			if(pdtmNeedToResolve == DateTime.MinValue)
			{
				return 0;
			}		

			int iRet = 0;
			try
			{
				foreach(DataRow drow in pdtbWorkingTime.Rows)
				{
					if(1 <= iRet && iRet <= 31)
					{
						return iRet;
					}
					else // if pdtmNeedToResolve is in any period, modify the iRet, and then the next loop will break, function return
					{
						DateTime dtmBeginDate = (DateTime)drow[BEGINDATE];
						DateTime dtmEndDate = (DateTime)drow[ENDDATE];
						DateTime dtmWorkTimeFrom = (DateTime)drow[WORKTIMEFROM];
						DateTime dtmWorkTimeTo = (DateTime)drow[WORKTIMETO];

						if(dtmBeginDate <= pdtmNeedToResolve && 	/* NeedToResolve > beginDate (yyyymmdd 00 00 00) */
							new DateTime(pdtmNeedToResolve.Year, pdtmNeedToResolve.Month, pdtmNeedToResolve.Day )  <= dtmEndDate)	/* start time of each Date of NeedToResolve <= EndDate  */
						{
							// slide the WorkTimeFrom (prototype 2005/01/01 xxxxxxx) to the actualWorkTimeFrom (2006/04/24 xxxx) 
							// where TimeNeedToResolve is 2006/04/24 yyy)
							DateTime dtmActualWorkTimeFrom = (new DateTime(pdtmNeedToResolve.Year, pdtmNeedToResolve.Month,pdtmNeedToResolve.Day)) /*Start Actual day*/
								.Add(	/* add the time amount from start of the day to the WorkTimeFrom day*/
								dtmWorkTimeFrom  .Subtract  (new DateTime(dtmWorkTimeFrom.Year, dtmWorkTimeFrom.Month, dtmWorkTimeFrom.Day)  )
								);
							DateTime dtmActualWorkTimeTo = dtmActualWorkTimeFrom.Add(
								dtmWorkTimeTo.Subtract(dtmWorkTimeFrom)
								);
							
							if(dtmActualWorkTimeFrom  <= pdtmNeedToResolve && pdtmNeedToResolve < dtmActualWorkTimeTo)	// RESOLVE is in the Shift worktime
							{
								int intDayDiff = dtmWorkTimeFrom.Day - 1;
								iRet = pdtmNeedToResolve.Day - intDayDiff;
							}	// end RESOLVE is in the Shift worktime

						}	// end resolve date is in the period worktime
					}
                    
				}	// end foreach datarow in REFERENCE TABLE
	
			}	// end try, there is error. perhap the cast action is fail, can't cast from DBNull
			catch
			{ 
				return 0;
			}

			return iRet;
		}

		/// <summary>
		/// values in pstrProductIDColName, pstrDayColName, pstrSumColName must not be NULL
		/// </summary>
		/// <param name="pdtbOriginal"></param>
		/// <param name="pstrProductIDColName"></param>
		/// <param name="pstrDayColName"></param>
		/// <param name="pstrSumColName"></param>
		/// <returns></returns>
		private DataTable SumAndGroupBy(DataTable pdtbOriginal, 
			string pstrProductIDColName,
			string pstrDayColName,
			string pstrSumColName)
		{
			DataTable dtbRet = pdtbOriginal.Clone();			

			ArrayList arrItem_Day = GetUniqueComlexKeyFromTable(pdtbOriginal, pstrProductIDColName, pstrDayColName);
			foreach(string strItemDay in arrItem_Day)
			{					
				string strFilter = string.Empty;			
				strFilter = 
					string.Format("[{0}]='{1}' AND [{2}]='{3}' ",
					pstrProductIDColName,
					strItemDay.Split('#')[0],
					pstrDayColName,
					strItemDay.Split('#')[1]					
					);				
				
				// GUIDE: get all rows of this Item from the dtbSourceData
				DataRow[] dtrows = pdtbOriginal.Select(strFilter);

				if(dtrows.Length > 0)
				{
					// Create DUMMYROW FIRST
					DataRow dtrNew = dtbRet.NewRow();

					double dblSumForThisItemDay = 0d;
					// GUIDE: for each rows in of this Item OF DTBSourceData - adjust the dblSumForThisItemDay
					foreach(DataRow dtr in dtrows)
					{					
						dblSumForThisItemDay += ReportBuilder.ToDouble( dtr[pstrSumColName] );
					}

					dtrNew[pstrProductIDColName] = dtrows[0][pstrProductIDColName];
					dtrNew[pstrDayColName] = dtrows[0][pstrDayColName];
					dtrNew[pstrSumColName] = dblSumForThisItemDay;

					dtbRet.Rows.Add(dtrNew);
				}
			}

			return dtbRet;
		}

		/// <summary>
		/// Get key pair: ProductID#Day
		/// Deliminate character is "#"
		/// </summary>
		/// <param name="pdtb"></param>
		/// <param name="pstrProductID"></param>
		/// <param name="pstrDay"></param>
		/// <returns></returns>
		private ArrayList GetUniqueComlexKeyFromTable(DataTable pdtb, string pstrProductID, string pstrDay)
		{
			ArrayList arrRet = new ArrayList();
			try
			{
				foreach (DataRow drow in pdtb.Rows)
				{	
					object objProductIDGet = drow[pstrProductID];
					object objDayGet = drow[pstrDay];
					string str = objProductIDGet.ToString().Trim() + "#" + objDayGet.ToString().Trim();
					if( !arrRet.Contains(str)  )
					{
						arrRet.Add(str);
					}
				}
			}
			catch
			{
				arrRet.Clear();
			}
			return arrRet;
		}

	}

}
